Re: Inconsistent behavior with AGE()

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: josh(at)agliodbs(dot)com
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Inconsistent behavior with AGE()
Date: 2004-10-28 22:22:45
Message-ID: 7548.1099002165@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Description: The age() built-in function sometimes truncates hours, minutes
> and seconds, and sometimes it doesn't, depending on the parameters. This
> seems inconsistent and confusing.

> gforge=> select age('2004-01-01'::TIMESTAMP);
> age
> ----------------
> 9 mons 27 days
> (1 row)

> gforge=> select age(now(),'2004-01-01'::TIMESTAMP);
> age
> ------------------------------------
> 9 mons 27 days 11:17:19.8895479999
> (1 row)

Actually, the definition of the single-parameter variants of age() is
age(current_date, $1)
not
age(now(), $1)

I'm not sure this is wrong, but perhaps it should be better documented.

Another point is that when you use now() (which returns timestamptz),
I believe you will get the timestamp promoted to timestamptz (which
introduces your timezone setting into the equation!) and then
the timestamptz variant of age() will be invoked. But age(TIMESTAMP)
is going to select the plain-timestamp variant, which will do a
non-DST-aware subtraction.

Thus for example:

regression=# select age(current_timestamp,'2004-01-01'::TIMESTAMP);
age
------------------------------------
9 mons 27 days 17:19:08.1852230001
(1 row)

regression=# select age(localtimestamp,'2004-01-01'::TIMESTAMP);
age
--------------------------------
9 mons 27 days 18:19:16.610111
(1 row)

regression=# select age(current_date,'2004-01-01'::TIMESTAMP);
age
----------------
9 mons 27 days
(1 row)

The first two answers differ by the amount of the (single) DST
transition that has occurred since 1/1. In another few days
they'd not differ anymore.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Josh Berkus 2004-10-28 22:34:12 Re: Inconsistent behavior with AGE()
Previous Message Josh Berkus 2004-10-28 20:56:25 Inconsistent behavior with AGE()