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

From Tom Lane
Subject Re: timestamp with time zone a la sql99
Date
Msg-id 22816.1098732904@sss.pgh.pa.us
Whole thread Raw
In response to Re: timestamp with time zone a la sql99  (Dennis Bjorklund <db@zigo.dhs.org>)
Responses Re: timestamp with time zone a la sql99
List pgsql-hackers
Dennis Bjorklund <db@zigo.dhs.org> writes:
> The standard treat days as a separate entry, it does not assume that a day 
> is 24 hours.

SQL92 says
        4.5.2  Intervals
        There are two classes of intervals. One class, called year-month        intervals, has an express or implied
datetimeprecision that in-        cludes no fields other than YEAR and MONTH, though not both are        required. The
otherclass, 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.

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.
        regards, tom lane


pgsql-hackers by date:

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