Re: SQL compliant interval implementation - Mailing list pgsql-hackers

From Josh Berkus
Subject Re: SQL compliant interval implementation
Date
Msg-id 200605231618.55139.josh@agliodbs.com
Whole thread Raw
In response to Re: SQL compliant interval implementation  ("Brendan Jurd" <direvus@gmail.com>)
List pgsql-hackers
Brendan,

> Could you elaborate on how it sucked?  Apart from the issue of
> daylight savings which Tom has mentioned, what are these limitations
> that needed to be worked around?

Well, actually, the DST thing was pretty severe -- it made timestamptz 
unusable.  That's why we partitioned interval into month/year | day/week | 
hour/minute/second/etc.

I personally don't see the benefit of evaluating "1 month" = "30 days", but 
I don't see the harm either.   "days" *don't* get rolled up to months, 
which is proper partitioned behavior:

postgres=# select interval '180 days';interval
----------180 days
(1 row)

postgres=# select interval '1800 days';interval
-----------1800 days

The only issue comes when you multiply units by a decimal:

postgres=# select interval '11 months' * 0.3;       ?column?
------------------------3 mons 8 days 24:00:00

... which leads to some broken calculations:

select ( interval '11 months' * 0.3 ) / 0.3;        ?column?
--------------------------10 mons 26 days 96:00:00

but anyone who deals in "fractional months" should know that they're 
approximating.  Previously, we couldn't get decimal calculations to work 
at all.
 > I've been searching through the archives for discussions relating to
> intervals, but haven't come across the one you're describing.  Most
> probably because there have been a LOT of discussions relating to
> intervals.

If I had a link, I'd send it.  But I'd have to do the same searching you're 
doing.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco


pgsql-hackers by date:

Previous
From: "Rodrigo Hjort"
Date:
Subject: LIKE, leading percent, bind parameters and indexes
Next
From: "Marc G. Fournier"
Date:
Subject: Re: Why is CVS server so slow?