Re: SQL compliant interval implementation

I don't see how this makese our system any better than it does not. It
just seems to eliminate the 30-day problem by not allowing it. That
doesn't seem to be a step forward.

---------------------------------------------------------------------------

Brendan Jurd wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +