Josh Berkus <josh@agliodbs.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