BUG #1332: wrong results from age function

On Mon, Nov 29, 2004 at 11:37:50 +0000,
PostgreSQL Bugs List <pgsql-bugs@postgresql.org> wrote:

The following bug has been logged online:

Bug reference: 1332
Logged by: Robert Grabowski

Email address: grabba@env.pl

PostgreSQL version: 7.4.6

Operating system: Linux

Description: wrong results from age function

Details:

select age('2004-02-01'::date, '2004-01-01'::date);
age
-------
1 mon
(1 row)

select age('2004-03-01'::date, '2004-02-01'::date);
age
---------
29 days
(1 row)

I think, it should be "1 mon".

I get "1 mon" when I try this:
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

area=> select age('2004-03-01'::date, '2004-02-01'::date);
age
-------
1 mon
(1 row)

I am actually using a version post 7.4.6 from cvs. I have also built
postgres using integer date time values.

Bruno Wolff III <bruno@wolff.to> writes:

I think, it should be "1 mon".

I get "1 mon" when I try this:

So do I. It might be timezone dependent though ... Robert, what
timezone setting are you using?

regards, tom lane

Tom Lane wrote:

Bruno Wolff III <bruno@wolff.to> writes:

I think, it should be "1 mon".

I get "1 mon" when I try this:

So do I. It might be timezone dependent though ... Robert, what
timezone setting are you using?

regards, tom lane

Hi!

It is some informactions from my PostgreSQL instalaction. Can it help
you?

Robert

$ psql -U root -d tmpl
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

tmpl=# select version();
version
---------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3
20040412 (Gentoo Linux 3.3.3-r6, ssp-3.3.2-2, pie-8.7.6)
(1 row)

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

tmpl=# select now();
now
-------------------------------
2004-12-01 16:47:46.522403+01
(1 row)

tmpl=# show DateStyle;
DateStyle
-----------
ISO, MDY
(1 row)

tmpl=# show lc_time;
lc_time
---------
C
(1 row)

tmpl=# show australian_timezones;
australian_timezones
----------------------
off
(1 row)

tmpl=#

Robert Grabowski <grabba@env.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