Re: timestamp with time zone a la sql99 - Mailing list pgsql-hackers

From Dennis Bjorklund
Subject Re: timestamp with time zone a la sql99
Date
Msg-id Pine.LNX.4.44.0410252204070.2015-100000@zigo.dhs.org
Whole thread Raw
In response to Re: timestamp with time zone a la sql99  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
On Mon, 25 Oct 2004, Tom Lane wrote:

>          There are two classes of intervals. One class, called year-month
>          intervals, has an express or implied datetime precision that in-
>          cludes no fields other than YEAR and MONTH, though not both are
>          required. The other class, called day-time intervals, has an ex-
>          press or implied interval precision that can include any fields
>          other than YEAR or MONTH.
> 
> AFAICS the reason for this rule is that they expect all Y/M intervals to
> be comparable (which they are) and they also expect all D/H/M/S intervals
> to be comparable, which you can only do by assuming that 1 D == 24 H.

I said I was not going to send any more mails, but here we go again :-)

The standard restrict the hour field to the interval 0-23, so there can 
never be any compare between for example '1 day 1 hour' and '25 hours'. 
This means that one can not add two intervals together to get a bigger 
one but that it would still work to do timestamp+interval+interval.

> It seems to me though that we can store days separately and do interval
> comparisons with the assumption 1 D == 24 H, and be perfectly
> SQL-compatible as far as that goes, and still make good use of the
> separate day info when adding to a timestamptz that has a DST-aware
> timezone.  In a non-DST-aware timezone the addition will act the same as
> if we weren't distinguishing days from h/m/s.  Therefore, an application
> using only the spec-defined features (ie, only fixed-numeric-offset
> timezones) will see no deviation from the spec behavior.

I agree with this.

-- 
/Dennis Björklund



pgsql-hackers by date:

Previous
From: Simon Riggs
Date:
Subject: Re: code question: storing INTO relation
Next
From: Josh Berkus
Date:
Subject: Re: timestamp with time zone a la sql99