Re: timestamp bug

Cedar Cox <cedarc(at)visionforisrael(dot)com> 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