Re: BUG #7722: extract(epoch from age(...)) appears to be broken

aanisimov@inbox.ru wrote:
> The following bug has been logged on the website:
>=20
> Bug reference:      7722
> Logged by:          Artem Anisimov
> Email address:      aanisimov@inbox.ru
> PostgreSQL version: 9.2.1
> Operating system:   Slackware Linux 14.0/amd64
> Description:       =20
>=20
> The following to queries give the same result (first arguments to age()
> differ in the day number only, second arguments are identical):
>=20
> select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23
> 15:56:10'));
>=20
> and
>=20
> select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23
> 15:56:10'));

alvherre=3D# select age('2012-11-22 16:41:31', '2012-10-23 15:56:10');
       age       =20
------------------
 30 days 00:45:21
(1 fila)

alvherre=3D# select age('2012-11-23 16:41:31', '2012-10-23 15:56:10');
      age      =20
----------------
 1 mon 00:45:21
(1 fila)

The problem is that age() returns 30 days in one case, and "one month" in=
 the
other; extract() then considers the month as equivalent to 30 days.  This=
 is
documented as such, see [1].

[1] http://www.postgresql.org/docs/current/static/functions-datetime.html

I think if you want a precise computation you should just subtract the tw=
o
dates and then extract epoch from the result.

alvherre=3D# select extract(epoch from timestamp '2012-11-22 16:41:31' - =
'2012-10-23 15:56:10');
 date_part=20
-----------
   2594721
(1 fila)

alvherre=3D# select extract(epoch from timestamp '2012-11-23 16:41:31' - =
'2012-10-23 15:56:10');
 date_part=20
-----------
   2681121
(1 fila)


--=20
=C1lvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services