From: Tom Lane <tgl@sss.pgh.pa.us>
To: gdavis@refractions.net
Cc: pgsql-sql@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: timestamp subtraction (was Re: formatting intervals with to_char)
Date: Thu, 05 Oct 2006 12:50:54 -0400
Message-ID: <21619.1160067054@sss.pgh.pa.us> (raw)
In-Reply-To: <4522EC42.1080500@refractions.net>
References: <4522EC42.1080500@refractions.net>
Graham Davis <gdavis@refractions.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, gdavis@refractions.net, pgsql-hackers@postgresql.org
Subject: Re: timestamp subtraction (was Re: formatting intervals with to_char)
In-Reply-To: <21619.1160067054@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox