Thread: BUG #7722: extract(epoch from age(...)) appears to be broken
The following bug has been logged on the website:
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: =
The following to queries give the same result (first arguments to age()
differ in the day number only, second arguments are identical):
select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23
15:56:10'));
and
select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23
15:56:10'));
The problem can also be reproduced in pgsql 9.1.4 of Fedora 17.
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
On Mon, Dec 3, 2012 at 03:05:57AM -0300, Alvaro Herrera wrote:
> aanisimov@inbox.ru wrote:
> > The following bug has been logged on the website:
> >
> > 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:
> >
> > The following to queries give the same result (first arguments to age()
> > differ in the day number only, second arguments are identical):
> >
> > select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23
> > 15:56:10'));
> >
> > and
> >
> > select extract(epoch from age('2012-11-22 16:41:31', '2012-10-23
> > 15:56:10'));
>
> alvherre=# select age('2012-11-22 16:41:31', '2012-10-23 15:56:10');
> age
> ------------------
> 30 days 00:45:21
> (1 fila)
>
> alvherre=# select age('2012-11-23 16:41:31', '2012-10-23 15:56:10');
> age
> ----------------
> 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
Wow, that is a weird case. In the first test, we count the number of
days because it is less than a full month. In the second case, we call
it a full month, but then forget how long it is. Not sure how we could
improve this.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
> Wow, that is a weird case. In the first test, we count the number of
> days because it is less than a full month. In the second case, we call
> it a full month, but then forget how long it is. Not sure how we could
> improve this.
I do not think this needs to be improved, the problem is given two
dates you can substract them in three different ways,
1.- (year months)+(days)+(hours minutes seconds), which is what age
does and is documented as such.
folarte=# select age('2013-11-24 16:41:31','2012-10-23 15:56:10');
age
-----------------------------
1 year 1 mon 1 day 00:45:21
(1 row)
Which is apropiate for things like 'I'm xxx old'
2.- (days)+(hours-minutes-seconds), which is what substractint dates
do ( or seems to do for me, as I've done:
select timestamp '2013-11-23 16:41:31' - '2012-10-23 15:56:10';
?column?
-------------------
396 days 00:45:21
Which I can not find a use for, but there sure are and I'm doomed to
find one soon.
3.- Exact duration ( I do this a lot at work as I need to calculate
call durations ):
folarte=# select extract(epoch from timestamp '2013-11-23 16:41:31') -
extract(epoch from timestamp '2012-10-23 15:56:10');
?column?
----------
34217121
(1 row)
folarte=# select (extract(epoch from timestamp '2013-11-23 16:41:31')
- extract(epoch from timestamp '2012-10-23 15:56:10')) * interval '1
second';
?column?
------------
9504:45:21
(1 row)
The problem I see is intervals are really complicated and difficult to
undestand, so it is at most a documentation problem ( people usually
understimate the difficulty of working with them, I see this a lot at
work ).
Francisco Olarte.
Dear Mr. Herrera and Mr. Momjian, thank you for your feedback and for explaining that age() better not be used. On Monday 03 December 2012 03:05:57 Alvaro Herrera wrote: > 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]. On Monday 03 December 2012 21:17:00 Bruce Momjian wrote: > Wow, that is a weird case. In the first test, we count the number of > days because it is less than a full month. In the second case, we call > it a full month, but then forget how long it is. Not sure how we could > improve this. Best regargs, Artem Anisimov.