Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id E07F847590F for ; Fri, 3 May 2002 16:38:19 -0400 (EDT) Received: from jbarilla.student.princeton.edu (jbarilla.student.Princeton.EDU [140.180.137.120]) by postgresql.org (Postfix) with ESMTP id 632A24758EE for ; Fri, 3 May 2002 16:38:18 -0400 (EDT) Received: (from jbarilla@localhost) by jbarilla.student.princeton.edu (8.11.6/8.11.6) id g43KcJM13984; Fri, 3 May 2002 16:38:19 -0400 To: pgsql-sql Subject: Odd behavior with timestamp/interval arithmetic From: Joseph Barillari X-Public-Key: finger -l jbarilla@phoenix.princeton.edu X-Public-Key-Fingerprint: 99C7 4F49 AF41 AD0F A4FC 529C 215E 1BD2 F6A1 FA37 X-URL: http://www.princeton.edu/~jbarilla Date: Fri, 03 May 2002 16:38:14 -0400 Message-ID: Lines: 59 User-Agent: Gnus/5.090007 (Oort Gnus v0.07) Emacs/21.2.50 (i686-pc-linux-gnu) MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha1; protocol="application/pgp-signature" X-Archive-Number: 200205/19 X-Sequence-Number: 7655 --=-=-= Content-Transfer-Encoding: quoted-printable I'm not very attuned to the subtleties of SQL, but this behavior seems downright strange: Adding an interval to a timestamp produces the expected result: cal=3D> select timestamp without time zone 'jan 1, 1999 00:00:00' + interv= al '1 month';=20=20 ?column?=20=20=20=20=20=20=20 --------------------- 1999-02-01 00:00:00 (1 row) But reversing the two produces nonsense: is this because the values are implicitly cast to type of the leftmost term in the expression? cal=3D> select interval '1 month' + timestamp without time zone 'jan 1, 19= 99 00:00:00';=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20 ?column?=20 ---------- 00:00:00 (1 row) This behavior is perfectly reasonable: cal=3D> select timestamp 'jan 1, 1999 00:00:00' + interval '1 month'; ?column?=20=20=20=20=20=20=20=20 ------------------------ 1999-02-01 00:00:00-05 (1 row) And yet, this expression, which just reverse the terms, is downright weird: cal=3D> select interval '1 month' + timestamp 'jan 1, 1999 00:00:00'; ?column?=20=20=20=20=20=20=20 --------------------- 1999-01-01 00:00:00 (1 row) Absolutely nothing happens! It is as if the '1 month' term did not exist.=20 Here's the weirdest part: cal=3D> select interval '1 month 2:30' + timestamp 'jan 1, 1999 00:00:00';= =20=20 ?column?=20=20=20=20=20=20=20 --------------------- 1999-01-01 02:30:00 (1 row) It picks up the 2:30, but not the 1 month! Is this the proper behavior? Or is there likely something wrong, either with PostgreSQL or my installation of it? Any comments would be appreciated. Thanks, Joe Barillari --=-=-= Content-Type: application/pgp-signature -----BEGIN PGP MESSAGE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: Processed by Mailcrypt 3.5.6 and Gnu Privacy Guard iD8DBQE80vU6IV4b0vah+jcRAjQNAKCUxFP6Axw3YDOILEl19fkGff2HHQCfYjdk vy85udIPLTSqDlXv6v7KTjM= =aXlG -----END PGP MESSAGE----- --=-=-=--