Received: from sss.pgh.pa.us (sss.pgh.pa.us [216.151.103.158]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f4KHUmA06952 for ; Sun, 20 May 2001 13:30:48 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f4KHUiR12140; Sun, 20 May 2001 13:30:44 -0400 (EDT) To: Cedar Cox cc: pgsql-sql@postgresql.org, Thomas Lockhart Subject: Re: timestamp bug In-reply-to: References: Comments: In-reply-to Cedar Cox message dated "Sun, 20 May 2001 12:49:00 +0300" Date: Sun, 20 May 2001 13:30:44 -0400 Message-ID: <12137.990379844@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200105/218 X-Sequence-Number: 3035 Cedar Cox writes: > devbarn71=# SELECT to_char(cast('2001 sep 5' as timestamp)+cast('3 days' as interval),'YYYY Mon DD'); > to_char > ------------- > 2001 Sep 07 > (1 row) Here in the US, the "funny" dates are in April and October: regression=# SELECT cast('2001 oct 27' as timestamp)+cast('1 days' as interval); ?column? ------------------------ 2001-10-28 00:00:00-04 (1 row) regression=# SELECT cast('2001 oct 27' as timestamp)+cast('2 days' as interval); ?column? ------------------------ 2001-10-28 23:00:00-05 (1 row) Perhaps if you want only date arithmetic, not correct-to-the-second arithmetic, you should use type date: regression=# SELECT cast('2001 oct 27' as date) + 1; ?column? ------------ 2001-10-28 (1 row) regression=# SELECT cast('2001 oct 27' as date) + 2; ?column? ------------ 2001-10-29 (1 row) However, this does bring up something I've thought about before. Type interval consists internally of two fields, months and seconds. Intervals specified in months, years, centuries, etc convert to so many months; everything in smaller-than-month units is converted to seconds. Then we can do timestamp arithmetic that copes with the fact that there's not a fixed equivalence between months and smaller units. But, when you think about DST jumps, it's obvious that this doesn't go far enough. There's not a fixed equivalence between days and smaller units either. ISTM that '2001-10-27 00:00:00-04'::timestamp + '2 days'::interval should yield '2001-10-29 00:00:00-05'::timestamp whereas '2001-10-27 00:00:00-04'::timestamp + '48 hours'::interval should yield '2001-10-28 23:00:00-05'::timestamp We cannot make that distinction now, but we could if type interval contained three fields internally: months, days, and seconds. regards, tom lane