Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VD2mq-0002p8-Hf for pgsql-docs@arkaria.postgresql.org; Sat, 24 Aug 2013 01:37:08 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1VD2mq-0007x1-0r for pgsql-docs@arkaria.postgresql.org; Sat, 24 Aug 2013 01:37:08 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VD2mo-0007wu-VM for pgsql-docs@postgresql.org; Sat, 24 Aug 2013 01:37:07 +0000 Received: from momjian.us ([72.94.173.45]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1VD2ml-0000Vg-4r for pgsql-docs@postgresql.org; Sat, 24 Aug 2013 01:37:05 +0000 Received: from bruce by momjian.us with local (Exim 4.72) (envelope-from ) id 1VD2mg-0005S4-Fb; Fri, 23 Aug 2013 21:36:58 -0400 Date: Fri, 23 Aug 2013 21:36:58 -0400 From: Bruce Momjian To: "Francisco Olarte (M)" Cc: Alvaro Herrera , aanisimov@inbox.ru, PostgreSQL-documentation Subject: Re: [BUGS] BUG #7722: extract(epoch from age(...)) appears to be broken Message-ID: <20130824013658.GA3412@momjian.us> References: <20121203060556.GA4601@alvh.no-ip.org> <20121204021700.GC7675@momjian.us> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="Kj7319i9nmIyA2yE" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.20 (2009-06-14) X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org --Kj7319i9nmIyA2yE Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + --Kj7319i9nmIyA2yE Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="dates.diff" 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 **** interval Subtract arguments, producing a symbolic result that ! uses years and months age(timestamp '2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days --- 6431,6437 ---- interval Subtract arguments, producing a symbolic result that ! uses years and months, rather than just days age(timestamp '2001-04-10', timestamp '1957-06-13') 43 years 9 mons 27 days *************** SELECT (DATE '2001-10-30', DATE '2001-10 *** 6794,6799 **** --- 6794,6829 ---- days because May has 31 days, while April has only 30. + + 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 EXTRACT(EPOCH FROM ...) and compute the + number of 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 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 timezone + = 'US/Eastern' and including a daylight saving time change, + illustrates these issues: + + + + SELECT EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') - + EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00'); + Result: 10537200 + SELECT (EXTRACT(EPOCH FROM timestamptz '2013-07-01 12:00:00') - + EXTRACT(EPOCH FROM timestamptz '2013-03-01 12:00:00')) + / 60 / 60 / 24; + Result: 121.958333333333 + SELECT timestamptz '2013-07-01 12:00:00' - timestamptz '2013-03-01 12:00:00'; + Result: 121 days 23:00:00 + SELECT age(timestamptz '2013-07-01 12:00:00', timestamptz '2013-03-01 12:00:00'); + Result: 4 mons + + <function>EXTRACT</function>, <function>date_part</function> --Kj7319i9nmIyA2yE Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs --Kj7319i9nmIyA2yE--