Thread: Ambiguous behaviour of age(timestamp)

Ambiguous behaviour of age(timestamp)

From
"Kouber Saparev"
Date:
Hello,

As the documentation describes the AGE() function -
http://developer.postgresql.org/docs/postgres/functions-datetime.html#FUNCTIONS-DATETIME-TABLE ,
there are two signatures:
- AGE(timestamp);
- AGE(timestamp, timestamp).

I think the documentation is misleading, because AGE(timestamp) is actually
converted to AGE(current_date, timestamp), and not to AGE(now(), timestamp).
So for example:
test=> select age(now());
       age
------------------
 -17:23:32.589739
(1 row)

I'm actually receiving the *age* of now() compared to the start of the day
0:00. Thus:
test=> select age(now() - interval '1 day');
       age
-----------------
 06:34:45.945311
(1 row)

returns not an age of 1 day but the age of yesterday's timestamp compared to
0:00 of today.

However:
test=> select age(now(), now() - interval '1 day');
  age
-------
 1 day
(1 row)

Which is correct.

As I initially understood the docs, the second form of age(timestamp,
timestamp) is the same as the first one - age(timestamp), with the exception
that the counting is not started from *today* but from custom timestamp
instead; and because the arguments are of type 'timestamp' and not 'date' I
supposed that now() is applied as a default first argument.

I am not sure what is the right behaviour of this function (I think it's not
correct now), but at least there have to be some explanations in the
documentation.

Regards,
Kouber Saparev

Re: Ambiguous behaviour of age(timestamp)

From
Tom Lane
Date:
"Kouber Saparev" <kouber@saparev.com> writes:
> I think the documentation is misleading, because AGE(timestamp) is actually
> converted to AGE(current_date, timestamp), and not to AGE(now(), timestamp).

Well, it says "Subtract from today", which I'd interpret as meaning
"current_date" not "now", but I made it say "Subtract from current_date"
just to be sure.

            regards, tom lane