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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers