The following bug has been logged online:

Bug reference: 1637
Logged by: J.Simon Goodall
Email address: simon (AT) EstanciaViamonte (DOT) com
PostgreSQL version: 7.4.2
Operating system: Linux
Description: age() function is giving different results
Details:

With the same query the age function is giving me two results, one as it
should be (34 years) and the other one is off by 1 hour, the query is a
single one so the results I asume should be the same.

I have tried finding the age(,) function in the source but had no luck.

Would appreciate some help, thx.

Simon.

Z SQL Method Z SQL Method at /StockMan/select_birthdays

Famname Firstname Birth date Age
Surname1 Name1 1971-05-05 34 years
Surname2 Name2 1964-05-05 40 years 11 mons 30 days 23:00:00

SQL used:

SELECT famname, firstname, birth_date, age( DATE '2005-05-05'::date,
birth_date::date )
FROM addresses
WHERE
EXTRACT ( day FROM timestamp '2005-05-05') = EXTRACT ( day FROM
birth_date)
AND
EXTRACT ( month FROM timestamp '2005-05-05') = EXTRACT ( month FROM
birth_date);

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

"J.Simon Goodall" <simon (AT) EstanciaViamonte (DOT) com> writes:
Daylight-savings time shift?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On 2005-04-28, "J.Simon Goodall" <simon (AT) EstanciaViamonte (DOT) com> wrote:
I did some analysis for this one when it was mentioned just now in the irc
chan. I can reproduce on 7.4.x as follows:

test=> set timezone to 'America/Buenos_Aires';
SET
test=> select age(date '2005-05-05', date '1964-05-05');
age
-----------------------------------
40 years 11 mons 30 days 23:00:00
(1 row)

Doesn't occur with any other timezones I tried, also doesn't occur if
you explicitly cast the dates to timestamp (not timestamptz) which
causes the other version of age() to be used instead. So this is obviously
a timezone-related issue. Couldn't reproduce on 8.0.1 with any timezone.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Andrew - Supernews <andrew+nonews (AT) supernews (DOT) com> writes:
Not for me --- I get "41 years" for that case. Since 7.4 depends on the
OS' timezone code, this is presumably OS-dependent. I'm using Fedora
Core 3, which has ... hmm ...

$ rpm -qf /usr/share/zoneinfo/America/Buenos_Aires
tzdata-2005f-1.fc3

.... a pretty recent zoneinfo package. What's yours?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On 2005-04-29, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:
I tested on a couple of FreeBSD 4.10 (approximately) builds, from May and
September last year. The original poster was on Linux I believe.

Looking at the definition of America/Buenos_Aires, which is exactly the
same in my FreeBSD sources and in the zoneinfo source supplied in pg 8.0.1,
it looks as though Argentina did indeed change timezones in 1969, so the
actual elapsed time between '2005-05-05 00:00:00' and '1964-05-05 00:00:00'
in that timezone is in fact 14974 days 23 hours.

Looking more closely, the significant thing seems to be that neither of my
7.4 servers is on 7.4.7, and this seems to have been fixed there (in response
to bug 1331).

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Andrew - Supernews <andrew+nonews (AT) supernews (DOT) com> writes:
1332 you mean. Yeah, I was testing on 7.4.7, so this is almost
certainly a duplicate of 1332.

2004-12-01 14:57 tgl

* src/backend/utils/adt/timestamp.c (REL7_4_STABLE): Fix
timestamptz_age() to do calculation in local timezone not GMT, per
bug 1332.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match