Folks,

Pardon me if this is redundant, but again the pgsql-bugs archives are not
working.

Summary: Various versions of AGE() give inconsistent results
Versions Tested: 7.4.2, 7.4.3, 8.0b4
Severity: Annoyance
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)

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(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

Josh Berkus <josh (AT) agliodbs (DOT) com> writes:
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

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

http://www.postgresql.org/docs/faqs/FAQ.html

Tom,

Hmmm ... well, if that's the definition, then we probably don't need to cha=
nge=20
it.=20=20

--Josh

--=20
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings