BUG #1637: age() function is giving different results
The following bug has been logged online:
Bug reference: 1637
Logged by: J.Simon Goodall
Email address: simon@EstanciaViamonte.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);
"J.Simon Goodall" <simon@EstanciaViamonte.com> writes:
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,
Daylight-savings time shift?
regards, tom lane
On 2005-04-28, "J.Simon Goodall" <simon@EstanciaViamonte.com> wrote:
The following bug has been logged online:
Bug reference: 1637
Logged by: J.Simon Goodall
Email address: simon@EstanciaViamonte.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.
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
Andrew - Supernews <andrew+nonews@supernews.com> writes:
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)
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
On 2005-04-29, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew - Supernews <andrew+nonews@supernews.com> writes:
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)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?
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
Andrew - Supernews <andrew+nonews@supernews.com> writes:
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).
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