Thread: 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