Re: SQL compliant interval implementation

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