Inconsistent behavior with AGE()

Josh Berkus <josh@agliodbs.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

Tom,

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.

Hmmm ... well, if that's the definition, then we probably don't need to change
it.

--Josh

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco