SQL compliant interval implementation - Mailing list pgsql-hackers

Hi all,

I've been looking at the postgres interval implementation lately, and
I'm interested in putting together an improved implementation that
accords more closely with the SQL specification, in particular with:

---
4.6.2 Intervals

There are two classes of intervals. One class, called year-month
intervals, has an express or implied datetime precision that includes
no fields other than YEAR and MONTH, though not both are required. The
other class, called day-time intervals, has an express or implied
interval precision that can include any fields other than YEAR or
MONTH.
---

The reason for intervals being divided into two classes is that
interval days and months (as distinct from datetime days and months)
have no well-defined relationship.

The current postgres implementation uses a conversion rate of 30 days
to the month, but this appears to be a band-aid solution to a deeper
problem; that postgres is trying to do with a single type something
which really should be done with two.

Imagine that I tried to implement a unified "length" type that
contained two fields, "metres" and "hobbits", where the number of
hobbits per metre differs from one hobbit to the next, but nonetheless
you're allowed to perform comparison and conversion between hobbits
and metres.  People would haul me out into the proverbial town square
and throw rotten tomatoes at me.  And rightly so.

I think the SQL standard has the right idea.  Keep the year-months and
the day-times separate.  Don't try to perform arithmetic or
comparisons between the two.

I note that this has been discussed on the mailing lists a few times
before, but I didn't see any conclusion or consensus reached.

So, the question is, are there any compelling reasons we shouldn't try
to implement "interval" as two types?  Does the existing unified
approach offer any significant advantages?

The only such advantage I can see is that it's easy for the user to
specify month values and day values together when performing date
arithmetic, for example if I wanted to add 1 month and 12 days to a
date, under the current approach I would simply issue:

> SELECT dateval + interval '1 month 12 days';

That's nice and convenient, however, there's no reason we can't keep
this simple under a separated approach:

> SELECT dateval + interval month '1' + interval second '12 days';

With ISO INTERVAL syntax (already listed as a TODO item) this becomes
a bit more human-readable:

> SELECT dateval + interval '1' month + interval '12' day;

By defining some convenient numeric input functions it becomes very
readable (not to mention incredibly easy to parse into the internal
format, versus textual inputs)

> SELECT dateval + months(1) + days(12);

It could be done without breaking existing applications; just
implement the two new interval types, and leave the old unified
interval in place as a deprecated type, then drop it after a few major
releases.

The day-time interval type would be pretty much the same as the
existing interval, with the month field removed.  The year-month field
would simply be a signed integer.

Thanks in advance for your comments.

Regards,
BJ