Gurjeet Singh <gurjeet@singh.im> writes:
> The interval arithmetic operations may also yield non-canonical
> values, and IMHO the 'interval op interval' or 'interval op scalar'
> expressions should yield an interval in canonical form.
You're mistaken.
> postgres=# select '6 days 00:16:00'::interval - '5 days
> 23:53:00'::interval as result;
> result
> -----------------
> 1 day -23:37:00
> postgres=# select '6 days 00:16:00'::interval + '5 days
> 23:53:00'::interval as result;
> result
> ------------------
> 11 days 24:09:00
> I cannot think of a use case where the above results are any better
> than emitting '00:23:00' and '12 days 00:09:00', respectively.
If that's what you want, use the justify_hours function. But that's
discarding information, so we're not going to force users to only
be able to get that form.
The reason why Postgres distinguishes '1 day' from '24 hours' is
pretty much the same as the reason it distinguishes '1 month' from
'30 days': adding those expressions to datetime values can produce
different results.
For example, since 2014-03-09 was a daylight-savings transition day
in my zone (US/Eastern),
regression=# select '2014-03-08 00:00'::timestamptz + '2 days'::interval; ?column?
------------------------2014-03-10 00:00:00-04
(1 row)
regression=# select '2014-03-08 00:00'::timestamptz + '48 hours'::interval; ?column?
------------------------2014-03-10 01:00:00-04
(1 row)
As for months vs. days:
regression=# select '2014-07-01'::date + '1 month'::interval; ?column?
---------------------2014-08-01 00:00:00
(1 row)
regression=# select '2014-07-01'::date + '30 days'::interval; ?column?
---------------------2014-07-31 00:00:00
(1 row)
> The ordering above demonstrates that Postgres _does_ consider '1 day
> -23:37:00' == '00:23:00', then it seems pointless to confuse the user
> by showing two different representations of the same datum.
Intervals are really three separate scalar values internally (months,
days, seconds). There isn't any way to handle that fully in a linear
sort order, so the comparison operators fall back to assuming 1 day
is equal to 24 hours (and 1 month is equal to 30 days). But that
doesn't make them the same for all purposes.
regards, tom lane
