MIME-Version: 1.0
In-Reply-To: <20121204021700.GC7675@momjian.us>
References: <E1TfEH5-0004JE-Qt@wrigleys.postgresql.org>
	<20121203060556.GA4601@alvh.no-ip.org>
	<20121204021700.GC7675@momjian.us>
From: "Francisco Olarte (M)" <folarte@peoplecall.com>
Date: Tue, 4 Dec 2012 20:18:31 +0100
Message-ID: 
 <CA+bJJbzvxnv2_mP4e6nH8Tx3mRuv_u0z7X3NQk+Sm+gLhUTZZA@mail.gmail.com>
Subject: Re: BUG #7722: extract(epoch from age(...)) appears to be broken
To: Bruce Momjian <bruce@momjian.us>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>, aanisimov@inbox.ru,
	pgsql-bugs@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Precedence: bulk
Sender: pgsql-bugs-owner@postgresql.org

> 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