The following bug has been logged online:

Bug reference: 1332
Logged by: Robert Grabowski

Email address: grabba (AT) env (DOT) 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".


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Mon, Nov 29, 2004 at 11:37:50 +0000,
PostgreSQL Bugs List <pgsql-bugs (AT) postgresql (DOT) org> wrote:
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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Bruno Wolff III <bruno (AT) wolff (DOT) to> writes:
So do I. It might be timezone dependent though ... Robert, what
timezone setting are you using?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Robert Grabowski <grabba (AT) env (DOT) pl> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom Lane wrote:
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=#

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org