Ambiguous behaviour of age(timestamp)

From: "Kouber Saparev" <kouber(at)saparev(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: Ambiguous behaviour of age(timestamp)
Date: 2004-11-29 15:33:32
Message-ID: 000701c4d628$c8a551d0$8100030a@pdc.imro.bg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2004-11-29 15:59:25 Re: BUG #1331: after update: vacuum blocks parallel to select
Previous Message PostgreSQL Bugs List 2004-11-29 11:37:50 BUG #1332: wrong results from age function