Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group