Re: [BUGS] Date calc bug

[ Forwarded to hackers list from bugs list ]

Olivier PRENANT <ohp(at)pyrenet(dot)fr> writes:
> ohp=> select '01-12-1999'::datetime + '@ 1 month - 1 sec' as bug;
> Thu 30 Dec 23:59:59 1999 MET

I see it here too, in a different timezone:

select '12-01-1999'::datetime + '@ 1 month - 1 sec' ;
Thu Dec 30 23:59:59 1999 EST

It's not a Y2K issue, because of this similar failure:

select '3-01-1999'::datetime + '@ 1month - 1 sec'::timespan;
Sun Mar 28 23:59:59 1999 EST

See the pattern? I suspect what is going on is that the low-order
(seconds) part of the timespan is being added in before the high-order
(months) part. If you did the calculation in two steps like this:

select '12-01-1999'::datetime + '@ - 1 sec'::timespan;
Tue Nov 30 23:59:59 1999 EST
select 'Tue Nov 30 23:59:59 1999 EST'::datetime + '@ 1 month'::timespan;
Thu Dec 30 23:59:59 1999 EST

then you'd think the result is reasonable.

The question for discussion is whether adding the months part and then
the seconds part would give more reasonable answers overall. Are there
other cases where doing it that way would yield nonintuitive results,
but the current code works?

Thomas, do you know why the datetime+timespan addition code works like
this? For that matter, is the internal representation of a timespan
going to continue to be months + seconds, or is that changing anyway?

regards, tom lane