Re: timestamp with time zone a la sql99

Tom,

> As far as I can tell, Dennis is planning slavish adherence to the spec,
> which will mean that the datatype is unable to cope effectively with
> daylight-savings issues.  So I'm unconvinced that it will be very
> helpful to you for remembering local time in addition to true
> (universal) time.

As somebody who codes calendar apps, I have to say that I have yet to see an
implementation of time zones which is at all useful for this purpose,
including the current implementation. My calendar apps on PostgreSQL 7.4
use "timestamp without time zone" and keep the time zone in a seperate field.

The reason is simple: our current implementation, which does include DST,
does not include any provision for the exceptions to DST -- such as Arizona
-- or for the difference between "1 day" and "24 hours". (Try adding "30
days" to "2004-10-05 10:00 PDT", you'll see what I mean). Nor do I see a
way out of this without raising the complexity, and configurability, level of
timezones significantly.

So if we're going to be broken (at least from the perspective of calendar
applications) we might as well be broken in a spec-compliant way.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> The reason is simple: our current implementation, which does include DST,
> does not include any provision for the exceptions to DST -- such as Arizona

Say what?

regression=# set timezone to 'MST7MDT';
SET
regression=# select now();
now
-------------------------------
2004-10-25 11:52:47.093538-06
(1 row)

regression=# set timezone to 'US/Arizona';
SET
regression=# select now();
now
-------------------------------
2004-10-25 10:52:49.441559-07
(1 row)

> -- or for the difference between "1 day" and "24 hours". (Try adding "30
> days" to "2004-10-05 10:00 PDT", you'll see what I mean).

This is the point about how interval needs to treat "day" as different
from "24 hours". I agree with that; the fact that it's not done already
is just a reflection of limited supply of round tuits. I think it's
orthogonal to the question of how flexible timestamp with time zone
needs to be, though.

> Nor do I see a way out of this without raising the complexity, and
> configurability, level of timezones significantly.

This does not seem to me to be an argument why timestamp with time zone
ought to be incapable of dealing with DST-aware time zones. That simply
guarantees that calendar apps won't be able to use the datatype. If
they still can't use it when it can do that, then we can look at the
next blocking factor.

> So if we're going to be broken (at least from the perspective of calendar
> applications) we might as well be broken in a spec-compliant way.

I have not said that we can't comply with the spec. I have said that
our ambitions need to be higher than merely complying with the spec.

regards, tom lane

Tom,

> regression=# set timezone to 'US/Arizona';
> SET
> regression=# select now();
> now
> -------------------------------
> 2004-10-25 10:52:49.441559-07

Wow! When did that get fixed? How do I keep track of this stuff if you
guys keep fixing it? ;-)

Of course, it would be very helpful if the result above could display
"Arizona" instead of the non-specific "-07", but I'm pretty sure that's
already a TODO.

> This is the point about how interval needs to treat "day" as different
> from "24 hours". I agree with that; the fact that it's not done already
> is just a reflection of limited supply of round tuits.

Well, when I first brought up the issue (2001) I was shot down on the basis of
spec-compliance, since SQL92 recognizes only Year/Month and
Day/Hour/Minute/etc. partitions. Glad it's up for consideration again.

Come to think of it, it was Thomas Lockhart who shot down the idea of fixing
Interval, and he's retired now ...

> This does not seem to me to be an argument why timestamp with time zone
> ought to be incapable of dealing with DST-aware time zones. That simply
> guarantees that calendar apps won't be able to use the datatype. If
> they still can't use it when it can do that, then we can look at the
> next blocking factor.

That's definitely a progressive attitude .... pardon me for being pessimistic.

> I have not said that we can't comply with the spec. I have said that
> our ambitions need to be higher than merely complying with the spec.

Hmmm ... well, does the spec specifically prohibit DST, or just leave it out?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> regression=# set timezone to 'US/Arizona';
>> SET
>> regression=# select now();
>> now
>> -------------------------------
>> 2004-10-25 10:52:49.441559-07

> Wow! When did that get fixed? How do I keep track of this stuff if you
> guys keep fixing it? ;-)

> Of course, it would be very helpful if the result above could display
> "Arizona" instead of the non-specific "-07", but I'm pretty sure that's
> already a TODO.

Well, that is *exactly what I'm talking about*. I want timestamp with
time zone to carry "US/Arizona" not just "-07". Obviously there needs
to be some option to get the latter displayed when that's all you want,
but internally a value of the datatype needs to be able to carry full
knowledge of which timezone it's supposed to be in. Dumbing that down
to a simple numeric GMT offset isn't good enough.

>> I have not said that we can't comply with the spec. I have said that
>> our ambitions need to be higher than merely complying with the spec.

> Hmmm ... well, does the spec specifically prohibit DST, or just leave it out?

It just doesn't talk about it AFAICS.

To comply with the spec we definitely need to be *able* to support
timezone values that are simple numeric GMT offsets. But I think we
ought also to be able to store values that are references to any of
the zic database entries. This looks to me like a straightforward
extension of the spec.

We went to all the trouble of importing src/timezone in order that we
could make a significant upgrade in our timezone capability, and now
it's time to take the steps that that enables. Before we were limited
to the lowest-common-denominator of the libc timezone routines on all
our different platforms, but now we are not...

regards, tom lane

On Mon, 25 Oct 2004, Josh Berkus wrote:

> Hmmm ... well, does the spec specifically prohibit DST, or just leave it
> out?

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.

The spec is not a full solution, it's also not a useless solution. I'm
happy as long as the spec is a subset of what pg implements. If not then I
would like to be able to have both but with different names or something
similar (but I think that should not be needed).

--
/Dennis Björklund

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. The issue is that the spec
defines Days/Weeks as being an agglomeration of hours and not an atomic
entity like Months/Years are. 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. The answer is to
try-partition INTERVAL values, as:

Hour/Minute/Second/ms
Day/Week
Month/Year

However, this could be considered to break the spec; certainly Thomas thought
it did. My defense is that the SQL committee made some mistakes, and
interval is a big one.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> 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. 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 think though that these points are closely related. The reason the
spec does that is exactly that they are ignoring DST and so they can
assume that 1 day == 24 hours == 86400 seconds. In a DST-aware world
you have to make a separation between days and the smaller units, just
as months are separated from smaller units because there's not a fixed
conversion factor.

To some extent the interval and timestamptz issues are orthogonal, but
I think it would be good to fix them in the same release if possible.
There will undoubtedly be some backwards-compatibility problems, and
I suppose that users would prefer to take them all at once than via
the chinese water torture method ...

> However, this could be considered to break the spec; certainly Thomas
> thought it did. My defense is that the SQL committee made some
> mistakes, and interval is a big one.

I'm not clear to what extent we have to actually break the spec, as
opposed to extend it, in order to do this to the "interval" type. To do
everything the spec says we need to do, we'll have to be able to make
some comparisons that aren't strictly valid (which amounts to assuming
that 1 day == 24 hours for some limited purposes) but we already do much
the same things with respect to months. (See other thread about whether
1 year == 360 days...)

regards, tom lane

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

On Mon, 25 Oct 2004, Josh Berkus wrote:

> Hour/Minute/Second/ms
> Day/Week
> Month/Year

And just when I pressed "send" on the previous mail I got the problem
:-)

--
/Dennis Björklund

On Mon, 25 Oct 2004, Josh Berkus wrote:

> Hour/Minute/Second/ms
> Day/Week
> Month/Year

This is embarrasing. I'm still a bit confused :-)

The standard treat days as a separate entry, it does not assume that a day
is 24 hours. It restricts the hour field to the interval 0-23 so one can
never have something like 25 hours. So it does not need to worry about how
many days that translate to.

And why do we need weeks also?

Well, this is the last mail I send before I've been thinking about this
for a while more :-)

--
/Dennis Björklund

Dennis Bjorklund <db(at)zigo(dot)dhs(dot)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 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.

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

On Mon, Oct 25, 2004 at 21:18:52 +0200,
Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org> wrote:
> On Mon, 25 Oct 2004, Josh Berkus wrote:
>
> > Hour/Minute/Second/ms
> > Day/Week
> > Month/Year
>
> This is embarrasing. I'm still a bit confused :-)
>
> The standard treat days as a separate entry, it does not assume that a day
> is 24 hours. It restricts the hour field to the interval 0-23 so one can
> never have something like 25 hours. So it does not need to worry about how
> many days that translate to.
>
> And why do we need weeks also?

For convenience. Just like years are a group of months, weeks are a group
of days.

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

Dennis,

> 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

Sounds like we're on the same page then.

> 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.

Hour field of the timestamp, or hour field of interval? There a world of
difference.

As long as we're willing to live with the understanding that +1day 1 hour may
produce a slightly different result than + 25 hours, I don't see the problem.
Currently I can add +900 hours if I like, postgreSQL will support it.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

On Mon, 25 Oct 2004, Josh Berkus wrote:

> > 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.
>
> Hour field of the timestamp, or hour field of interval? There a world of
> difference.

Hour field of an interval can be 0-23 according to the spec (doesn't say
that we need that restriction, but we do need to understand what the spec
say).

--
/Dennis Björklund

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> As long as we're willing to live with the understanding that +1day 1 hour may
> produce a slightly different result than + 25 hours, I don't see the problem.

Right, which is exactly why we can't accept the spec's restriction that
the hour field be limited to 0-23. People may legitimately want to add
48 hours to a timestamp, and *not* have that mean the same as adding
"2 days". Besides, we would have a backwards-compatibility problem if
we tried to forbid it, since as you note we've always accepted such input.

regards, tom lane

>>>regression=# set timezone to 'US/Arizona';
>>>SET
>>>regression=# select now();
>>>now
>>>-------------------------------
>>>2004-10-25 10:52:49.441559-07
>
>
>>Wow! When did that get fixed? How do I keep track of this stuff if you
>>guys keep fixing it? ;-)

That's worked for ages. What doesn't work is this:

usatest=# select current_timestamp at time zone 'US/Arizona';
ERROR: time zone "us/arizona" not recognized

Chris

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:
> That's worked for ages. What doesn't work is this:

> usatest=# select current_timestamp at time zone 'US/Arizona';
> ERROR: time zone "us/arizona" not recognized

Right, and similarly you can do

regression=# select '2004-10-25 21:32:33.430222 MST'::timestamptz;
timestamptz
-------------------------------
2004-10-26 00:32:33.430222-04
(1 row)

but not

regression=# select '2004-10-25 21:32:33.430222 US/Arizona'::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "2004-10-25 21:32:33.430222 US/Arizona"

I would like to see both of these cases working in 8.1; and furthermore
I'd like to see the timezone specs coming back as entered, not as bare
numeric offsets. (This will need to be adjustable via a DateStyle
option, of course, but I want the information to be in there whether it
is displayed or not.)

regards, tom lane