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. +