Skip site navigation (1) Skip section navigation (2)

timestamp subtraction (was Re: formatting intervals with to_char)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: gdavis(at)refractions(dot)net
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: timestamp subtraction (was Re: formatting intervals with to_char)
Date: 2006-10-05 16:50:54
Message-ID: 21619.1160067054@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Graham Davis <gdavis(at)refractions(dot)net> writes:
> The documentation for to_char states that:

> "|to_char(interval)| formats HH and HH12 as hours in a single day, while 
> HH24 can output hours exceeding a single day, e.g. >24."

> However I can not get it to work with time intervals that span more than 
> 1 day.

Well, it does in fact print intervals exceeding 24 hours:

regression=# select to_char('48 hours'::interval, 'HH24:MI:SS');
 to_char
----------
 48:00:00
(1 row)

However, '48 hours' and '2 days' are not the same thing.  The problem
with the case you give is really that timestamp_mi applies justify_hours
to its result --- that is, 

regression=# select ('2006-09-15 23:59:00'::timestamp - '2006-09-01 09:30:41'::timestamp);
     ?column?
------------------
 14 days 14:28:19
(1 row)

should be reporting '350:28:19' instead.

This is a hack that was done to minimize the changes in the regression
test expected outputs when we changed type interval from months/seconds
to months/days/seconds.  But I wonder whether it wasn't a dumb idea.
It is certainly inconsistent, as noted in the code comments.

I'm tempted to propose that we remove the justify_hours call, and tell
anyone who really wants the old results to apply justify_hours() to the
subtraction result for themselves.  Not sure what the fallout would be,
though.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Luke LonerganDate: 2006-10-05 16:52:33
Subject: Re: Query Failed, out of memory
Previous:From: Andrew DunstanDate: 2006-10-05 16:49:59
Subject: Re: Query Failed, out of memory

pgsql-sql by date

Next:From: Martin MarquesDate: 2006-10-05 18:27:46
Subject: age() vs. timestamp substraction
Previous:From: Andrew SullivanDate: 2006-10-05 15:29:52
Subject: Re: i have table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group