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

From: [email-masked]
Date: Sun, 02 Dec 2012 18:28:19 +0000

The following bug has been logged on the website:

Bug reference:      7722
Logged by:          Artem Anisimov
Email address:      [email-masked]
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.

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

---

From: Alvaro Herrera <[email-masked]>
Date: Mon, 3 Dec 2012 03:05:57 -0300

> select extract(epoch from age('2012-11-23 16:41:31', '2012-10-23

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

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

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 3 Dec 2012 21:17:00 -0500

> alvherre=# select age('2012-11-22 16:41:31', '2012-10-23 15:56:10');

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  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

---

From: Артем Анисимов <[email-masked]>
Date: Tue, 04 Dec 2012 09:16:14 +0400

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

> it a full month, but then forget how long it is.  Not sure how we could

Best regargs,
Artem Anisimov.

---

From: "Francisco Olarte (M)" <[email-masked]>
Date: Tue, 4 Dec 2012 20:18:31 +0100

> it a full month, but then forget how long it is.  Not sure how we could

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.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 23 Aug 2013 21:36:58 -0400

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

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

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 425544a..a411f86
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
*** 6431,6437 ****
          </entry>
          <entry><type>interval</type></entry>
          <entry>Subtract arguments, producing a <quote>symbolic</> result that
!         uses years and months</entry>
          <entry><literal>age(timestamp '2001-04-10', timestamp '1957-06-13')</literal></entry>
          <entry><literal>43 years 9 mons 27 days</literal></entry>
         </row>
--- 6431,6437 ----
          </entry>
          <entry><type>interval</type></entry>
          <entry>Subtract arguments, producing a <quote>symbolic</> result that
!         uses years and months</entry>, rather than just days
          <entry><literal>age(timestamp '2001-04-10', timestamp '1957-06-13')</literal></entry>
          <entry><literal>43 years 9 mons 27 days</literal></entry>
         </row>
*************** SELECT (DATE '2001-10-30', DATE '2001-10
*** 6794,6799 ****
--- 6794,6829 ----
     days</> because May has 31 days, while April has only 30.
    </para>
  
+   <para>
+    Subtraction of dates and timestamps can also be complex.  The most
+    accurate way to perform subtraction is to convert each value to a number
+    of seconds using <literal>EXTRACT(EPOCH FROM  ...)</> and compute the
+    number of <emphasis>seconds</> between the two values.  This will adjust
+    for the number of days in each month, timezone changes, and daylight
+    saving time adjustments.  Operator subtraction of date or timestamp
+    values returns the number of days (24-hours) and hours/minutes/seconds
+    between the values, making the same adjustments.  The <function>age</>
+    function returns years, months, days, and hours/minutes/seconds,
+    performing field-by-field subtraction and then adjusting for negative
+    field values.  The following queries, produced with <literal>timezone
+    = 'US/Eastern'</> and including a daylight saving time change,
+    illustrates these issues:
+   </para>
+ 
+ <screen>
+ SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
+        EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00');
+ <lineannotation>Result: </lineannotation><computeroutput>10537200</computeroutput>
+ SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') -
+         EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00')) 
+         / 60 / 60 / 24;
+ <lineannotation>Result: </lineannotation><computeroutput>121.958333333333</computeroutput>
+ SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00';
+ <lineannotation>Result: </lineannotation><computeroutput>121 days 23:00:00</computeroutput>
+ SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00');
+ <lineannotation>Result: </lineannotation><computeroutput>4 mons</computeroutput>
+ </screen>
+ 
    <sect2 id="functions-datetime-extract">
     <title><function>EXTRACT</function>, <function>date_part</function></title>

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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 3 Sep 2013 13:25:11 -0400

> illustration that our documentation is lacking in this area.

Patch applied.  It will appear in PG 9.4.

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 03 Sep 2013 15:59:57 -0400

> 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

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 3 Sep 2013 16:06:36 -0400

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

Oops, sorry.  Fixed.  My apologies.