Re: BUG #1332: wrong results from age function

Robert Grabowski <grabba(at)env(dot)pl> writes:
> Tom Lane wrote:
>> So do I. It might be timezone dependent though ... Robert, what
>> timezone setting are you using?

> tmpl=# show TimeZone;
> TimeZone
> ----------
> unknown
> (1 row)

That's not real helpful :-( ... but guessing that you are in
Europe/Prague zone, I tried

regression=# set TimeZone TO 'Europe/Prague';
SET
regression=# select age('2004-03-01'::date, '2004-02-01'::date);
age
---------
29 days
(1 row)

So it is a timezone-related issue. Furthermore, this still works:

regression=# select age('2004-03-01'::timestamp, '2004-02-01'::timestamp);
age
-------
1 mon
(1 row)

age() only comes in timestamp and timestamptz flavors, so when you use
"date" inputs the timestamptz flavor is preferred.

Tracing through the source code, I see that timestamp_age and
timestamptz_age are coded exactly the same, meaning that in the
timestamptz case the inputs are converted to GMT time, so what
the code is looking at is effectively
age('2004-02-29 23:00'::timestamp, '2004-01-31 23:00'::timestamp);
I think returning "29 days" for that is not unreasonable. The bug
is instead that we shouldn't be doing the arithmetic in GMT zone.
Seems it would be better to break down both times in the local time zone.
The reason we don't see the funny behavior in zones west of Greenwich is
that, say,
age('2004-03-01 05:00'::timestamp, '2004-02-01 05:00'::timestamp);
still gives the desired result. But it would doubtless be better if
the computation were being done as
age('2004-03-01 00:00'::timestamp, '2004-02-01 00:00'::timestamp);
which means we need to use a local-time-aware breakdown.

One question is whether, if the two timestamps have different GMT
offsets (due to a DST transition between), we should factor that into
the age result or not. My feeling is "not" --- you don't want to
see "1 month 1 hour" as the result even if in some sense it would be
correct.

So the proposed fix is to add tz and tzn parameters to the
timestamp2tm() calls in timestamptz_age() (so that the breakdown is done
in local time) but then ignore the tz values while doing the
subtraction.

Comments?

regards, tom lane