From: | Gurjeet Singh <gurjeet(at)singh(dot)im> |
---|---|
To: | PGSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Interval arithmetic should emit interval in canonical format |
Date: | 2014-07-15 17:25:21 |
Message-ID: | CABwTF4W-xt7pUGLoFqVSfd-cHaVE1UxD1436w_naJKkiMyrDJw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Sawada Masahiko | 2014-07-15 17:29:46 | Re: timeout of pg_receivexlog --status-interval |
Previous Message | Lars Ewald (web.de) | 2014-07-15 16:51:39 | Fwd: Re: Compile fails on AIX 6.1 |