It's hard to argue that the current behaviour is wrong, but it's worth a try.
First I'd appreciate the "official" reasons why Postgres prefers to
keep interval values in non-canonical form, like '1 day -23:37:00'
instead of '00:23:00'. I understand it has something to do with a
year/month/day not being exactly 365-days/30-days/24-hours, and/or
operations involving interval and 'timestamp with time zone'. But
since it's not explicitly spelled out in docs or in code (at least I
didn't find it in the obvious places), seeking explanation here. I
understand that the answers may obviate any change in behaviour I am
requesting below.
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. For eg.
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.
We may not be able to turn every interval datum into canonical form,
but at least the intervals produced as a result of interval operators
can be converted to canonical form to reduce surprises for users. I
may even go as far as proposing rounding up 24-hours into a day, but
not round up days into months or months into years.
I was surprised by the presence of non-canonical form of interval in a
sorted-by-interval result set. The intervals were computed within the
query, using 'timestamp without time zone' values in a table.
# select ...
result
--------
...
00:23:00
00:23:00
1 day -23:37:00
00:23:00
00:22:00
...
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. This also
increases the code complexity required in applications/ORMs to parse
interval data's text representation.
Best regards,
--
Gurjeet Singh http://gurjeet.singh.im/
EDB : www.EnterpriseDB.com : The Enterprise PostgreSQL Company
