Re: timestamp with time zone a la sql99

On Mon, 25 Oct 2004, Josh Berkus wrote:

> Dennis,
>
> > It doesn't discuss it. According to the spec a timestamp with time zone is
> > a UTC value + a HH:MM offset from GMT. And intervals in the spec is either
> > a year-month value or a day-time value. One can only compare year-month
> > values with each other and day-time values with each other. So they avoid
> > the problem of the how many days is a month by not allowing it.
>
> That's not what Tom and I were talking about.

You wanted to know what the standard said, and I told what I knew.

> The issue is that the spec defines Days/Weeks as being an agglomeration
> of hours and not an atomic entity like Months/Years are.

I don't know what you mean with this. The standard does treat them as

year
month
day
hour
minute
second (with fractions)

There is no weeks there, if that is what you mean.

> This leads to some wierd and calendar-breaking behavior when combined
> with DST, for example:
>
> template1=> select '2004-10-09 10:00 PDT'::TIMESTAMPTZ + '45 days'::INTERVAL
> template1-> ;
> ?column?
> ------------------------
> 2004-11-23 09:00:00-08
> (1 row)
>
> Because of the DST shift, you get an hour shift which is most decidely not
> anything real human beings would expect from a calendar.

I don't see how the above can be caused by the representation of an
interval. The above timestamp is

2004-10-09 10:00 PDT

which in the standard would be

2004-10-09 10:00 -07

and after the additon would be

2004-11-23 10:00:00-07

Here the time zone is wrong since the standard does not know about named
zones and dst.

An implementation like the one Tom (and I) want would start with

2004-10-09 10:00 PDT

and then after the addition one would get

2004-11-23 10:00:00 PST

At least that's my understanding of what we want and what we can get (plus
that we also need to support HH:MM tz values since those also exist in the
world, check this emails header for example).

It's possible that you discuss something else, but that has been lost on
me so far.

--
/Dennis Björklund