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

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]http://www.postgresql.org/docs/current/static/functions-datetime.html.

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

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

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

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

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

I have developed the attached doc patch to address your very clear
illustration that our documentation is lacking in this area.

---------------------------------------------------------------------------

On Tue, Dec 4, 2012 at 08:18:31PM +0100, Francisco Olarte (M) 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.

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.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Fri, Aug 23, 2013 at 09:36:58PM -0400, Bruce Momjian wrote:

I have developed the attached doc patch to address your very clear
illustration that our documentation is lacking in this area.

Patch applied. It will appear in PG 9.4.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

On 9/3/13 1:25 PM, Bruce Momjian wrote:

On Fri, Aug 23, 2013 at 09:36:58PM -0400, Bruce Momjian wrote:

I have developed the attached doc patch to address your very clear
illustration that our documentation is lacking in this area.

Patch applied. It will appear in PG 9.4.

This broke the documentation build:

openjade:func.sgml:6434:37:E: character data is not allowed here

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

On Tue, Sep 3, 2013 at 03:59:57PM -0400, Peter Eisentraut wrote:

On 9/3/13 1:25 PM, Bruce Momjian wrote:

On Fri, Aug 23, 2013 at 09:36:58PM -0400, Bruce Momjian wrote:

I have developed the attached doc patch to address your very clear
illustration that our documentation is lacking in this area.

Patch applied. It will appear in PG 9.4.

This broke the documentation build:

openjade:func.sgml:6434:37:E: character data is not allowed here

Oops, sorry. Fixed. My apologies.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs