Re: Timezone database changes

I had a thought a week ago. If we update the time zone database for
future dates, and you have a future date/time stored, doesn't the time
change when the time zone database changes.

For example if I schedule an appointment in New Zealand for 10:00a and
we change the time zone database so that date is now daylight savings,
doesn't the time change to display as 9 or 11am? That seems pretty bad.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On 10/8/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> I had a thought a week ago. If we update the time zone database for
> future dates, and you have a future date/time stored, doesn't the time
> change when the time zone database changes.
>
> For example if I schedule an appointment in New Zealand for 10:00a and
> we change the time zone database so that date is now daylight savings,
> doesn't the time change to display as 9 or 11am? That seems pretty bad.

As a general rule, when you're doing planning or calendar type
applications where times need to be treated in local time, you never
store them in any other form (such as UTC). If you need to work with
multiple zones, you also store the timezone and do explicit
conversions on demand. In database terms, that means using "timestamp
without time zone" and some other column for the zone.

Put another way, when the authoritative reference is local time and
not absolute time, you don't use absolute time :)

I'm sure this trips up a lot of people, but it's S.O.P. for any
environment. OS services have the same caveats, and I've seen desktop
apps make this mistake and have to correct it later. (PostgreSQL
actually provides better support for time zones than some
environments. I've seen some use the current offset for conversions
of all times, which utterly breaks in the face of DST; others take DST
into account, but using the current year's DST rules only.)

It might be worth trying to document for PostgreSQL-using people to
find, but I don't see any need for behavior changes. Or anything
practical that could be done, for that matter.

I wrote:
> On 10/8/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > I had a thought a week ago. If we update the time zone database for
> > future dates, and you have a future date/time stored, doesn't the time
> > change when the time zone database changes.
> >
> > For example if I schedule an appointment in New Zealand for 10:00a and
> > we change the time zone database so that date is now daylight savings,
> > doesn't the time change to display as 9 or 11am? That seems pretty bad.
>
> As a general rule, when you're doing planning or calendar type
> applications where times need to be treated in local time, you never
> store them in any other form (such as UTC). If you need to work with
> multiple zones, you also store the timezone and do explicit
> conversions on demand. In database terms, that means using "timestamp
> without time zone" and some other column for the zone.

Actually, I'm used to knowing how PostgreSQL does it, but looking at
things again I remember some confusion I had when first encountering
the timestamp types. I don't know what the SQL Standard says; is the
implication that "timestamp with time zone" actually stores the
literal time and the zone it is associated with? (Would make more
sense, given the name.)

If that's true, then the current behavior is a bug^H^H^Hdocumented
limitation. I still don't know of anything practical that could be
done now, but...

Trevor Talbot wrote:

> Actually, I'm used to knowing how PostgreSQL does it, but looking at
> things again I remember some confusion I had when first encountering
> the timestamp types. I don't know what the SQL Standard says; is the
> implication that "timestamp with time zone" actually stores the
> literal time and the zone it is associated with? (Would make more
> sense, given the name.)

SQL itself doesn't say anything how the data element should be stored,
only how it should be operated upon. It do, however,say that a
datetime/time WITH TIME ZONE represents the time in UTC (SQL 2003,
§4.3). All operations on the element are defined as if it's an instance
in time (in UTC).

Interestingly, if you cast a TIMESTAMP WITH TIME ZONE to a character
value, it should be converted with the _original_ time zone value (SQL
2003, §5.8) _unless_ you specify "AT LOCAL".

In the database, it makes sense to store the time instance in UTC (for
efficiency) and only apply the offset for presentation.

--Magne

Am Dienstag, 9. Oktober 2007 schrieb Magne Mæhre:
> SQL itself doesn't say anything how the data element should be stored,
> only how it should be operated upon.  It do, however,say that a
> datetime/time WITH TIME ZONE represents the time in UTC (SQL 2003,
> §4.3).  All operations on the element are defined as if it's an instance
> in time (in UTC).

There is, generally, a significant mismatch between the time zone handling
specified in SQL and practical requirements. More specifically, SQL only
supports time zones with fixed offsets and does not support daylight-saving
time rules at all.

Independent of what any specification might say, however, the currently
implemented behavior is clearly wrong in my mind and needs to be fixed.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On 10/9/07, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

> Independent of what any specification might say, however, the currently
> implemented behavior is clearly wrong in my mind and needs to be fixed.

I don't think it's wrong, just a particular choice. As an example,
consider an interval scheduling system that handles everything in
absolute time (UTC), but uses local time as a convenience. Perhaps it
presents a timestamp a few months from now to the user, and accepts
any stamp back in the user's timezone. When the DST rules suddenly
change a couple weeks before that timestamp occurs, you don't want the
database changing its interpretation of what was entered months ago;
the absolute time is already the correct time.

That's simply a specific version of the general case of wanting the
database to operate in absolute time, and present local time as a user
convenience. Conveniently, PostgreSQL does exactly that now.

If that behavior changes, making the above work anyway is easy:
explicitly convert to UTC on input. But that's just a counterpoint to
what I mentioned earlier in the thread, explicit conversion of local
times. Either way, someone has to do some work to adapt to their
specific usage, so which method the database naturally uses is just an
arbitrary choice.

FWIW, I am in favor of having it [behave as if it does] store the
literal time and its associated zone. To me that seems smart,
consistent, and more likely to fit what people need. I don't see it
as fixing wrong behavior, though.

>>> On Mon, Oct 8, 2007 at 10:48 PM, in message
<200710090348(dot)l993mOG15547(at)momjian(dot)us>, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> I had a thought a week ago. If we update the time zone database for
> future dates, and you have a future date/time stored, doesn't the time
> change when the time zone database changes.
>
> For example if I schedule an appointment in New Zealand for 10:00a and
> we change the time zone database so that date is now daylight savings,
> doesn't the time change to display as 9 or 11am? That seems pretty bad.

It depends. It's what you want if you are looking to point your telescope
to the right part of the sky or to be on an international conference call
which isn't going to be rescheduled because of New Zealand's daylight
saving time rules; but, as you point out, not usually what you want for a
local appointment.

We use TIMESTAMP WITH TIME ZONE to capture a moment in the natural stream
of time, and separate DATE and TIME WITHOUT TIME ZONE to capture local
appointments. I believe this gives the desired behavior both with
ANSI/ISO standard behavior and with the PostgreSQL implementation.

-Kevin

>>> On Tue, Oct 9, 2007 at 6:49 AM, in message
<470B6AD4(dot)6080502(at)sun(dot)com>, Magne
Mæhre <Magne(dot)Mahre(at)Sun(dot)COM> wrote:
>
> Interestingly, if you cast a TIMESTAMP WITH TIME ZONE to a character

> value, it should be converted with the _original_ time zone value
(SQL
> 2003, *5.8) _unless_ you specify "AT LOCAL".

A lot of the ANSI/ISO behavior is broken if TIMESTAMP WITH TIME ZONE
does
not include the time zone. One of the least standards compliant areas
of
PostgreSQL is the date/time arithmetic; but any attempt to implement
the
standard date math will fail until the time zone is part of the WITH
TIME
ZONE data types.

-Kevin

Am Dienstag, 9. Oktober 2007 schrieb Trevor Talbot:
> I don't think it's wrong, just a particular choice.  As an example,
> consider an interval scheduling system that handles everything in
> absolute time (UTC), but uses local time as a convenience.

We are not considering an interval scheduling system, we are considering a
database system. Such a system should have the basic property that if you
store A, it will read out as A. The money type is similarly buggy: if you
change the locale, the semantic value of the data changes. With money type,
the problem is obvious and easy to recognize. With the timestamp with time
zone type, however, the problem is much more subtle and will likely go
unnoticed by many who will then be unpunctual for their appointments.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Tue, Oct 09, 2007 at 05:04:39PM +0200, Peter Eisentraut wrote:
> We are not considering an interval scheduling system, we are considering a
> database system. Such a system should have the basic property that if you
> store A, it will read out as A. The money type is similarly buggy: if you
> change the locale, the semantic value of the data changes. With money type,
> the problem is obvious and easy to recognize. With the timestamp with time
> zone type, however, the problem is much more subtle and will likely go
> unnoticed by many who will then be unpunctual for their appointments.

For both money and timestamps the taggedtypes module provides exactly
what you want. It stores the timezone/currency as entered and displays
that when output. Sometimes that's what you want, sometimes it's not.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Trevor Talbot wrote:
> I wrote:
> > On 10/8/07, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > I had a thought a week ago. If we update the time zone database for
> > > future dates, and you have a future date/time stored, doesn't the time
> > > change when the time zone database changes.
> > >
> > > For example if I schedule an appointment in New Zealand for 10:00a and
> > > we change the time zone database so that date is now daylight savings,
> > > doesn't the time change to display as 9 or 11am? That seems pretty bad.
> >
> > As a general rule, when you're doing planning or calendar type
> > applications where times need to be treated in local time, you never
> > store them in any other form (such as UTC). If you need to work with
> > multiple zones, you also store the timezone and do explicit
> > conversions on demand. In database terms, that means using "timestamp
> > without time zone" and some other column for the zone.
>
> Actually, I'm used to knowing how PostgreSQL does it, but looking at
> things again I remember some confusion I had when first encountering
> the timestamp types. I don't know what the SQL Standard says; is the
> implication that "timestamp with time zone" actually stores the
> literal time and the zone it is associated with? (Would make more
> sense, given the name.)
>
> If that's true, then the current behavior is a bug^H^H^Hdocumented
> limitation. I still don't know of anything practical that could be
> done now, but...

Do we need additional documention about this?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

>>> On Tue, Oct 9, 2007 at 12:11 PM, in message
<200710091711(dot)l99HB9x15798(at)momjian(dot)us>, Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> Trevor Talbot wrote:
>>
>> Actually, I'm used to knowing how PostgreSQL does it, but looking at
>> things again I remember some confusion I had when first encountering
>> the timestamp types. I don't know what the SQL Standard says; is the
>> implication that "timestamp with time zone" actually stores the
>> literal time and the zone it is associated with? (Would make more
>> sense, given the name.)

I don't see how the ANSI functionality can work without it.

>> If that's true, then the current behavior is a bug^H^H^Hdocumented
>> limitation. I still don't know of anything practical that could be
>> done now, but...
>
> Do we need additional documention about this?

Probably, but we need a lot more than that to conform to the standard
and to avoid surprising behavior. The first of the two statements
below is valid ANSI syntax to add one day to the current moment. It
is accepted and generates the wrong value. The second is the
PostgreSQL way. It is one of many anomalies.

bigbird=> select current_timestamp, current_timestamp + interval '1' day;
now | ?column?
-------------------------------+-------------------------------
2007-10-09 12:47:18.876498-05 | 2007-10-09 12:47:18.876498-05
(1 row)

bigbird=> select current_timestamp, current_timestamp + interval '1 day';
now | ?column?
-------------------------------+-------------------------------
2007-10-09 12:47:20.190999-05 | 2007-10-10 12:47:20.190999-05
(1 row)

-Kevin

Kevin Grittner wrote:
> Probably, but we need a lot more than that to conform to the standard
> and to avoid surprising behavior.  The first of the two statements
> below is valid ANSI syntax to add one day to the current moment.

That's the lack of standard interval support, which is an entirely
separate issue.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> We are not considering an interval scheduling system, we are considering a
> database system. Such a system should have the basic property that if you
> store A, it will read out as A.

I'm not sure that I think this sort of rigid thinking works very well in
the wonderland that is date/time behavior. When the rules of the game
(ie, DST laws) are changing underneath you, who is to say exactly what
"reading out as A" means? Arguably, TIMESTAMP WITH TIME ZONE does the
right thing now, and would cease to do the right thing if we changed
it as I think you intend.

Given that all involved agree that the SQL spec is hopelessly broken
in this area, becoming more compliant with it is not a goal that I
think we should strive for blindly.

regards, tom lane

Am Mittwoch, 10. Oktober 2007 schrieb Tom Lane:
> I'm not sure that I think this sort of rigid thinking works very well in
> the wonderland that is date/time behavior.  When the rules of the game
> (ie, DST laws) are changing underneath you, who is to say exactly what
> "reading out as A" means?  Arguably, TIMESTAMP WITH TIME ZONE does the
> right thing now, and would cease to do the right thing if we changed
> it as I think you intend.

If we make an appointment at 12-November-2007 at 10:00 CET (winter time) and
next week those in charge decide to postpone the change to winter time from
28-October-2007 to 25-November-2007, what becomes of the appointment? Do we
still meet when the hands point to "10", or when?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

* Peter Eisentraut <peter_e(at)gmx(dot)net> [071010 09:58]:

> If we make an appointment at 12-November-2007 at 10:00 CET (winter time) and
> next week those in charge decide to postpone the change to winter time from
> 28-October-2007 to 25-November-2007, what becomes of the appointment? Do we
> still meet when the hands point to "10", or when?

And to make matters worse, what if your appointment includes a audio(or
video) conference with colleagues sitting in London, who you've told to
meet at 16:00 (OK, my timezones/daylight savings, etc may be off)

So, do you meet when the hands point at 10, or do they meet when the
hands point at 4?

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk <aidan(at)highrise(dot)ca> writes:
> * Peter Eisentraut <peter_e(at)gmx(dot)net> [071010 09:58]:
>> If we make an appointment at 12-November-2007 at 10:00 CET (winter time) and
>> next week those in charge decide to postpone the change to winter time from
>> 28-October-2007 to 25-November-2007, what becomes of the appointment? Do we
>> still meet when the hands point to "10", or when?

> And to make matters worse, what if your appointment includes a audio(or
> video) conference with colleagues sitting in London, who you've told to
> meet at 16:00 (OK, my timezones/daylight savings, etc may be off)

Exactly ... there is more than one right answer here. The answer that
PG's TIMESTAMP WITH TIME ZONE code deems to be right is that UTC is
reality. That's a definition that is indeed useful for a wide variety
of real-world problems. In a lot of cases where it's not so useful,
TIMESTAMP WITHOUT TIME ZONE does the right thing. I'm not sure that
there's a significant use-case for a third behavior, and I definitely
don't think you can make an argument from first principles that the
UTC-based definition is wrong.

(FWIW, Red Hat has been struggling with this exact problem of
cross-time-zone meeting times for some years now, and has pretty much
arrived at the conclusion that company meeting times are to be defined
in UTC...)

The arguments that have been made for storing a zone along with the UTC
value seem to mostly boil down to "it should present the value the same
way I entered it", but if you accept that argument then why do we have
DateStyle? If it's OK to regurgitate "11-12-2007" as "2007-12-11",
I'm not clear on why adjusting timezone isn't OK.

regards, tom lane

Tom Lane wrote:
> Exactly ... there is more than one right answer here. The answer that
> PG's TIMESTAMP WITH TIME ZONE code deems to be right is that UTC is
> reality. That's a definition that is indeed useful for a wide variety
> of real-world problems. In a lot of cases where it's not so useful,
> TIMESTAMP WITHOUT TIME ZONE does the right thing. I'm not sure that
> there's a significant use-case for a third behavior, and I definitely
> don't think you can make an argument from first principles that the
> UTC-based definition is wrong.
>
> (FWIW, Red Hat has been struggling with this exact problem of
> cross-time-zone meeting times for some years now, and has pretty much
> arrived at the conclusion that company meeting times are to be defined
> in UTC...)
>
> The arguments that have been made for storing a zone along with the UTC
> value seem to mostly boil down to "it should present the value the same
> way I entered it", but if you accept that argument then why do we have
> DateStyle? If it's OK to regurgitate "11-12-2007" as "2007-12-11",
> I'm not clear on why adjusting timezone isn't OK.

I am thinking additional documention is the only good solution here.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On 10/10/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The arguments that have been made for storing a zone along with the UTC
> value seem to mostly boil down to "it should present the value the same
> way I entered it", but if you accept that argument then why do we have
> DateStyle? If it's OK to regurgitate "11-12-2007" as "2007-12-11",
> I'm not clear on why adjusting timezone isn't OK.

Actually, what I meant at least (not sure if others meant it), is
storing the value in the timezone it was entered, along with what zone
that was. That makes the value stable with respect to the zone it
belongs to, instead of being stable with respect to UTC. When DST
rules change, the value is in effect "reinterpreted" as if it were
input using the new rules. To me that's also what the name of the
type suggests it does.

I imagine internally it would convert each value to UTC just before
performing any calculations on it, and generally be irritating to work
with. But the public interface would do the "other" right thing.

Well, for political time zones anyway. I have no idea what that
approach is supposed to do with numeric offsets, or the old "PST8PDT"
type stuff.

Anyway, getting back to documentation, I think it's just necessary to
somehow point out the difference between these two behaviors in the
section about the date and time types, and which type is more
appropriate for which situation. I don't know if there's enough room
to provide effective examples without getting too bogged down in
details though.

"Trevor Talbot" <quension(at)gmail(dot)com> writes:
> Actually, what I meant at least (not sure if others meant it), is
> storing the value in the timezone it was entered, along with what zone
> that was. That makes the value stable with respect to the zone it
> belongs to, instead of being stable with respect to UTC. When DST
> rules change, the value is in effect "reinterpreted" as if it were
> input using the new rules.

What happens if the rules change in a way that makes the value illegal
or ambiguous (ie, it now falls into a DST gap)?

But perhaps more to the point, please show use-cases demonstrating that
this behavior is more useful than the pure-UTC behavior. For storage of
actual time observations, I think pure-UTC is unquestionably the more
useful. Peter's example of a future appointment time is a possible
counterexample, but as observed upthread it's hardly clear which
behavior is more desirable in such a case.

regards, tom lane

Am Mittwoch, 10. Oktober 2007 schrieb Tom Lane:
> Peter's example of a future appointment time is a possible
> counterexample, but as observed upthread it's hardly clear which
> behavior is more desirable in such a case.

Whereas the most realistic solution to my example might be, "the parties
involved reconfirm their appointment", I expect that public transportation
companies such as railways and airlines have specific rules to deal with
these situations. That might give us some insight what the
industrial-strength resolution could be, even if we deem it inappropriate to
implement it at the end. So if someone has knowledge in that area, I'd be
interested.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Trevor Talbot wrote:
>, what I meant at least (not sure if others meant it), is
> storing the value in the timezone it was entered, along with what zone
> that was. That makes the value stable with respect to the zone it
> belongs to, instead of being stable with respect to UTC. When DST
> rules change, the value is in effect "reinterpreted" as if it were
> input using the new rules. To me that's also what the name of the
> type suggests it does.

I would argue that this isn't necessarily more helpful than what we have.
Many of us work in an in an international environment, and DST rules (and,
I'm sure you all remember the Venezuela case, time zones) change, and
at different instances in time.

To reiterate what the SQL standard says, a WITH TIMEZONE element should
have information on the original time zone it was stored as, but only in
the form of an offset from UTC in hours and minutes. SQL has no
notion of time zone labels, so if we decide to store these, we wouldn't
be any closer to SQL compliancy. An interesting observation is that,
as far as I can tell, the original time zone is only applied when casting
the element to a string. Apart from that, it's not used.

I would suggest that the WITH TIMEZONE elements are converted to UTC when
inserted into the database. Since all operations on it are based on
its UTC form, it's most efficient ( I believe) if the data is stored that
way. To be compliant, an offset (hours and minutes) to the time zone
that was used when storing the time should be stored as well.

--Magne

=?ISO-8859-1?Q?Magne_M=E6hre?= <Magne(dot)Mahre(at)Sun(dot)COM> writes:
> I would suggest that the WITH TIMEZONE elements are converted to UTC when
> inserted into the database. Since all operations on it are based on
> its UTC form, it's most efficient ( I believe) if the data is stored that
> way. To be compliant, an offset (hours and minutes) to the time zone
> that was used when storing the time should be stored as well.

Well, the question is what would we *do* with the latter? If we have
that override the TimeZone zone for output, we will break a lot of
things. There's also the question of what to put into a computed
timestamp value. Consider

regression=# select timestamptz '2007/10/01 00:00 EDT';
timestamptz
------------------------
2007-10-01 00:00:00-04
(1 row)

regression=# select timestamptz '2007/10/01 00:00 EDT' + interval '3 months';
?column?
------------------------
2008-01-01 00:00:00-05
(1 row)

I think the latter behavior (that you get midnight EST not EDT) is
generally agreed to be desirable, but I don't see any very principled
way to achieve it if UTC offsets (as opposed to timezones) are
considered "sticky".

The proposal to store a zone identifier (*not* a raw UTC offset)
is somewhat more defensible but it's still got issues.

regards, tom lane

>>> On Wed, Oct 10, 2007 at 12:23 PM, in message
<470D0A90(dot)9000401(at)sun(dot)com>, Magne
Mæhre <Magne(dot)Mahre(at)Sun(dot)COM> wrote:
> An interesting observation is that,
> as far as I can tell, the original time zone is only applied when
casting
> the element to a string. Apart from that, it's not used.

It's been a while since I looked at it, but my recollection is that
much of the standard date/time math which people assert can't handle
practical use cases do work if the timestamps and times WITH TIME
ZONE have a time zone in the offset-from-UTC format. I have yet to
see anyone provide an example where this isn't true; if anyone has
such an example, it would be instructive to see it.

-Kevin

>>> On Wed, Oct 10, 2007 at 2:51 PM, in message
<470CE6E2(dot)EE98(dot)0025(dot)0(at)wicourts(dot)gov>, "Kevin Grittner"
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>>>> On Wed, Oct 10, 2007 at 12:23 PM, in message
> <470D0A90(dot)9000401(at)sun(dot)com>, Magne
> Mæhre <Magne(dot)Mahre(at)Sun(dot)COM> wrote:
>> An interesting observation is that,
>> as far as I can tell, the original time zone is only applied when
> casting
>> the element to a string. Apart from that, it's not used.
>
> It's been a while since I looked at it, but my recollection is that
> much of the standard date/time math which people assert can't handle
> practical use cases do work if the timestamps and times WITH TIME
> ZONE have a time zone in the offset-from-UTC format. I have yet to
> see anyone provide an example where this isn't true; if anyone has
> such an example, it would be instructive to see it.

Withdrawn. Tom posted an example today which crossed mine in the
mail.
I see his point.

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> It's been a while since I looked at it, but my recollection is that
> much of the standard date/time math which people assert can't handle
> practical use cases do work if the timestamps and times WITH TIME
> ZONE have a time zone in the offset-from-UTC format.

Certainly --- as long as you are considering a fixed UTC offset, the
standard does what it claims to. The knock on it is that in the real
world people want sane behavior with real-world timezone definitions
that have non-constant UTC offsets.

As an example, timestamptz '2007-01-01 00:00 -05' + interval '6 months'
must yield 2007-07-01 00:00 -05 according to the spec, AFAICS; but most
people living in the EST5EDT zone would prefer to get midnight -04.
There are probably some folk in South America who'd prefer midnight
-06. (Looks at a map ... hm, maybe not, but certainly Europe vs
Africa would produce some such examples.)

regards, tom lane

On 10/10/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Trevor Talbot" <quension(at)gmail(dot)com> writes:
> > Actually, what I meant at least (not sure if others meant it), is
> > storing the value in the timezone it was entered, along with what zone
> > that was. That makes the value stable with respect to the zone it
> > belongs to, instead of being stable with respect to UTC. When DST
> > rules change, the value is in effect "reinterpreted" as if it were
> > input using the new rules.
>
> What happens if the rules change in a way that makes the value illegal
> or ambiguous (ie, it now falls into a DST gap)?

That's a good question. I have a vague memory of something that
absolutely needed to accept such values (as this would have to)
choosing a reasonable way to interpret them. In the case of jumps
forward, e.g. 1:59->3:00, a time of 2:15 is assumed to be on the
previous scale, and thus interpreted as 3:15. For overlapping times,
it picks one but I don't recall which.

Unfortunately I don't remember where I picked that up. It might have
been a semi-standard, or it might have been someone's personal theory.

Your later example of midnight EDT + 3 months wanting to be midnight
EST is a good one, so what I said earlier about internally converting
to UTC is not something you want to do eagerly. I'd wondered why
upthread Kevin mentioned using separate date and time types instead of
just using timestamp; now I know. This point should go in any
documentation enhancement too.

> But perhaps more to the point, please show use-cases demonstrating that
> this behavior is more useful than the pure-UTC behavior. For storage of
> actual time observations, I think pure-UTC is unquestionably the more
> useful. Peter's example of a future appointment time is a possible
> counterexample, but as observed upthread it's hardly clear which
> behavior is more desirable in such a case.

Actually, it usually is, because a human picked one ahead of time.
For example, if the appointment is set for 3pm in London, the London
zone is the authoritative one, so that's what you store it in the DB
as. If you're viewing it in NZ time, and the NZ DST rules change, so
does what you see. If the London rules change, what you see in NZ
still changes, but what you see in London does not.

Choosing UTC in that scenario only works if the London DST rules don't
change. Choosing the referencing timezone (London) when you store the
value works if either one changes.

If an organization is regularly scheduling such things, they might
just settle on UTC anyway to avoid confusion, in which case you store
values in UTC and get the same behavior as you do currently.

I don't know what this person was doing, but I gather sticky timezones
was preferable to them:
http://archives.postgresql.org/pgsql-general/2007-08/msg00461.php

Thinking that it might have had out of date zone rules brings up an
interesting scenario though. Consider a closed (no networking or
global interest) filing system in a local organization's office, where
it's used to record the minutes of meetings and such via human input.
It would seem that the correct time to record in that case is in fact
the local time, not UTC. If that system is left alone for years, and
does not receive any zone rule updates, it will likely begin storing
the wrong UTC values. When the data is later transported out
(upgrade, archive, whatever), it will be incorrect unless you use that
particular snapshot of the zone rules.

That situation might sound a bit contrived, but I think the real point
is that even for some records of observed times, the local time is the
authoritative one, not UTC.

Trevor Talbot wrote:
> Thinking that it might have had out of date zone rules brings up an
> interesting scenario though. Consider a closed (no networking or
> global interest) filing system in a local organization's office, where
> it's used to record the minutes of meetings and such via human input.
> It would seem that the correct time to record in that case is in fact
> the local time, not UTC. If that system is left alone for years, and
> does not receive any zone rule updates, it will likely begin storing
> the wrong UTC values. When the data is later transported out
> (upgrade, archive, whatever), it will be incorrect unless you use that
> particular snapshot of the zone rules.
>
> That situation might sound a bit contrived, but I think the real point
> is that even for some records of observed times, the local time is the
> authoritative one, not UTC.

...and for that scenario you have TIMESTAMP WITHOUT TIME ZONE

--Magne

Tom Lane wrote:

> As an example, timestamptz '2007-01-01 00:00 -05' + interval '6 months'
> must yield 2007-07-01 00:00 -05 according to the spec, AFAICS; but most
> people living in the EST5EDT zone would prefer to get midnight -04.
> There are probably some folk in South America who'd prefer midnight
> -06. (Looks at a map ... hm, maybe not, but certainly Europe vs
> Africa would produce some such examples.)

Correct me if I'm wrong, but IIRC there is no universally accepted
canonical list of time zone names (labels). By using the name, instead
of the offset, you are not guaranteed that one database client can even
understand the timestamp entered by another client (unless the database
contains its own definition which is backed up and restored together
with the data).

I clearly understand the problems (having written calendar applications
myself) of not relating the time to the specific time zone, but I think
not adhering to the standard is much worse. If you're only interested
in one time zone, simply use WITHOUT TIME ZONE, and you're good. if
you're working in multiple time zones, the arithmetic and handling will
be complex (and maybe not even deterministic).

I think the resolution and presentation of local time is best handled by
an application level, and not by the database.

--Magne

On 10/11/07, Magne Mæhre <Magne(dot)Mahre(at)sun(dot)com> wrote:
> Trevor Talbot wrote:
> > Thinking that it might have had out of date zone rules brings up an
> > interesting scenario though. Consider a closed (no networking or
> > global interest) filing system in a local organization's office, where
> > it's used to record the minutes of meetings and such via human input.
> > It would seem that the correct time to record in that case is in fact
> > the local time, not UTC. If that system is left alone for years, and
> > does not receive any zone rule updates, it will likely begin storing
> > the wrong UTC values. When the data is later transported out
> > (upgrade, archive, whatever), it will be incorrect unless you use that
> > particular snapshot of the zone rules.
> >
> > That situation might sound a bit contrived, but I think the real point
> > is that even for some records of observed times, the local time is the
> > authoritative one, not UTC.
>
> ...and for that scenario you have TIMESTAMP WITHOUT TIME ZONE

But that doesn't give you DST-sensitive display for free, which is
tempting for application use, especially if the application is meant
to be suitably generic.

"Trevor Talbot" <quension(at)gmail(dot)com> writes:
> On 10/11/07, Magne M=E6hre <Magne(dot)Mahre(at)sun(dot)com> wrote:
>> Trevor Talbot wrote:
>>> That situation might sound a bit contrived, but I think the real point
>>> is that even for some records of observed times, the local time is the
>>> authoritative one, not UTC.
>>
>> ...and for that scenario you have TIMESTAMP WITHOUT TIME ZONE

> But that doesn't give you DST-sensitive display for free, which is
> tempting for application use, especially if the application is meant
> to be suitably generic.

If you are dealing only in local time, what do you need timezone for at
all?

Also note the possibility of coercing one type to the other on-the-fly
for display, or using the AT TIME ZONE construct.

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Trevor Talbot" <quension(at)gmail(dot)com> writes:
>> On 10/11/07, Magne M=E6hre <Magne(dot)Mahre(at)sun(dot)com> wrote:
>>> Trevor Talbot wrote:
>>>> That situation might sound a bit contrived, but I think the real point
>>>> is that even for some records of observed times, the local time is the
>>>> authoritative one, not UTC.
>>>
>>> ...and for that scenario you have TIMESTAMP WITHOUT TIME ZONE
>
>> But that doesn't give you DST-sensitive display for free, which is
>> tempting for application use, especially if the application is meant
>> to be suitably generic.
>
> If you are dealing only in local time, what do you need timezone for at
> all?
>
> Also note the possibility of coercing one type to the other on-the-fly
> for display, or using the AT TIME ZONE construct.

I think there are clearly use cases for all three semantics:

1) Specified time of day in whatever the current time zone is
(i.e. our current TIMESTAMP WITHOUT TIME ZONE)

2) Specific moment in time
(i.e. stored in UTC which is unaffected by time zone rules)

3) Specified time of day in specified time zone
(equivalent to #2 except when the time zone rules change)

In the SQL spec #2 and #3 are interchangeable since the time zone rules there
can never change. But in the real world as we've seen they do.

Surely #2 is a must-have. There has to be a data type for representing a fixed
moment in time unaffected by any time zone rules. Anything recording events --
which of course occurred at a specific moment in time -- needs it and there
are a whole lot of databases which do just that. Actually in my experience
most tables have one or sometimes more timestamps of that nature.

The lack of #3 doesn't seem terribly pressing given how rarely the time zone
rules change. Even with the latest shenanigans I don't think anyone's run into
any unexpected problems.

I would say if someone implemented #3 then it would make sense to have it. It
would probably make sense for calendaring applications where the user is
manually entering a timezone and probably means that time in that timezone
even if the moment in time that it represents changes due to the rules
changing.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On 10/11/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> > "Trevor Talbot" <quension(at)gmail(dot)com> writes:
> >> On 10/11/07, Magne Mæhre <Magne(dot)Mahre(at)sun(dot)com> wrote:
> >>> Trevor Talbot wrote:
> >>>> That situation might sound a bit contrived, but I think the real point
> >>>> is that even for some records of observed times, the local time is the
> >>>> authoritative one, not UTC.
> >>>
> >>> ...and for that scenario you have TIMESTAMP WITHOUT TIME ZONE
> >
> >> But that doesn't give you DST-sensitive display for free, which is
> >> tempting for application use, especially if the application is meant
> >> to be suitably generic.
> >
> > If you are dealing only in local time, what do you need timezone for at
> > all?

October 29, 2006, 1:15am: PDT or PST?

Even if you ignore overlap points like that, DST status is a piece of
semantic information the human retrieving the data may want to know.
It doesn't make much sense for an app to avoid the database's
perfectly good knowledge of the local timezone to get it.

> > Also note the possibility of coercing one type to the other on-the-fly
> > for display, or using the AT TIME ZONE construct.

Sure, but that's simply a workaround like tagging different zones
yourself is. This single case isn't terribly important, it's just a
non-future-appointment one where remembering the local zone makes
sense.

If we change it a bit so that it regularly transports data to a
central office, you still want to know what time zone it belongs to.
Right now, the local office's zone rules matter because you need it to
convert to UTC properly. Instead, it should be the central office's
zone rules that matter for temporary conversion and reporting, because
you really don't want the original data changed at all. The original
data is the legitimate record, not the conversion to UTC.

This can all be done manually by applications today, of course. It
would just be nice to take advantage of PostgreSQL's time zone
knowledge more easily in these situations.

> 2) Specific moment in time
> (i.e. stored in UTC which is unaffected by time zone rules)
>
> 3) Specified time of day in specified time zone
> (equivalent to #2 except when the time zone rules change)

> Surely #2 is a must-have. There has to be a data type for representing a fixed
> moment in time unaffected by any time zone rules. Anything recording events --
> which of course occurred at a specific moment in time -- needs it and there
> are a whole lot of databases which do just that. Actually in my experience
> most tables have one or sometimes more timestamps of that nature.

While I agree that UTC storage is definitely a needed option, I was
trying to point out in the scenario above that sometimes an event
recorded at a specific moment in time *is* local time. Birth
certificates aren't in UTC. Usually there's no practical difference,
but there can be a semantic difference.

> The lack of #3 doesn't seem terribly pressing given how rarely the time zone
> rules change. Even with the latest shenanigans I don't think anyone's run into
> any unexpected problems.

The link I posted upthread was someone who ran into something
unexpected. There wasn't enough detail to figure out what, exactly,
just that something related to zones changed and surprised them.

And no, I don't think it's urgent either; the current behavior is
known and fairly easy to understand. It's just that some applications
need a different set of semantics.

=?ISO-8859-1?Q?Magne_M=E6hre?= <Magne(dot)Mahre(at)Sun(dot)COM> writes:
> Correct me if I'm wrong, but IIRC there is no universally accepted
> canonical list of time zone names (labels).

Yeah; we have an agreed-on list of names for the purposes of PG, namely
the names shown by pg_timezone_names, but that list changes from time to
time. What's worse, it might be different on some platforms than others
even for the same PG release, since some distros override our zic
database with the system's own.

So one of the problems that would have to be surmounted to have a
datatype that embeds a zone ID is to figure out a persistent, but
compact, ID to assign to each zone ...

regards, tom lane

"Trevor Talbot" <quension(at)gmail(dot)com> writes:

>> 2) Specific moment in time
>> (i.e. stored in UTC which is unaffected by time zone rules)
>>
>> 3) Specified time of day in specified time zone
>> (equivalent to #2 except when the time zone rules change)
>
>> Surely #2 is a must-have. There has to be a data type for representing a fixed
>> moment in time unaffected by any time zone rules. Anything recording events --
>> which of course occurred at a specific moment in time -- needs it and there
>> are a whole lot of databases which do just that. Actually in my experience
>> most tables have one or sometimes more timestamps of that nature.
>
> While I agree that UTC storage is definitely a needed option, I was
> trying to point out in the scenario above that sometimes an event
> recorded at a specific moment in time *is* local time. Birth
> certificates aren't in UTC. Usually there's no practical difference,
> but there can be a semantic difference.

Thinking of it as UTC is the wrong way to think about it. A birth occurred at
a specific moment in time. You want to record that precise moment, not what it
happened to show on the clock at the time. If the clock turns out to have been
in the wrong timezone the birth isn't going to move.

The use case for storing a local timestamp with a timezone attached is for
things like appointments. If the time zone rules change you would want the
appointment to move with them, not to stay at the same moment in time.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

On 10/11/07, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> "Trevor Talbot" <quension(at)gmail(dot)com> writes:

> > While I agree that UTC storage is definitely a needed option, I was
> > trying to point out in the scenario above that sometimes an event
> > recorded at a specific moment in time *is* local time. Birth
> > certificates aren't in UTC. Usually there's no practical difference,
> > but there can be a semantic difference.
>
> Thinking of it as UTC is the wrong way to think about it. A birth occurred at
> a specific moment in time. You want to record that precise moment, not what it
> happened to show on the clock at the time. If the clock turns out to have been
> in the wrong timezone the birth isn't going to move.

Neither is the birth certificate. The recorded, legal time of the
birth is the one that was written down. If it doesn't happen to match
an international notion of current time, that's unfortunate, but it's
not subject to arbitrary changes later. Even if it does match, it
still belongs to a specific time zone. That's the key semantic point:
regurgitating that time as anything other than exactly what it was
entered as is simply not correct.

Birth dates enter common usage with the time zone stripped. Your
birthday doesn't change when you move across a date line, despite the
fact that it's tied to the zone you were born in.

And yet it's an observed and recorded event, not a predicted appointment.

"Trevor Talbot" <quension(at)gmail(dot)com> writes:
> Neither is the birth certificate. The recorded, legal time of the
> birth is the one that was written down. If it doesn't happen to match
> an international notion of current time, that's unfortunate, but it's
> not subject to arbitrary changes later. Even if it does match, it
> still belongs to a specific time zone. That's the key semantic point:
> regurgitating that time as anything other than exactly what it was
> entered as is simply not correct.

I'm not convinced about that. One consideration I think you are failing
to account for is that there is a big difference between past and future
times, at least in terms of what is likely to be the meaning of a
change. The above reasoning might apply to a past time but I think it's
bogus for a future time. If the TZ offset for a future time changes,
it's likely because of a DST law change, and we are in Peter's
what-time-is-the-appointment scenario. A TZ offset for a past time
probably should not change, but if it does, it suggests a retroactive
data correction. Surely you don't intend to prevent people from fixing
bad data?

regards, tom lane

On 10/11/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Trevor Talbot" <quension(at)gmail(dot)com> writes:
> > Neither is the birth certificate. The recorded, legal time of the
> > birth is the one that was written down. If it doesn't happen to match
> > an international notion of current time, that's unfortunate, but it's
> > not subject to arbitrary changes later. Even if it does match, it
> > still belongs to a specific time zone. That's the key semantic point:
> > regurgitating that time as anything other than exactly what it was
> > entered as is simply not correct.
>
> I'm not convinced about that. One consideration I think you are failing
> to account for is that there is a big difference between past and future
> times, at least in terms of what is likely to be the meaning of a
> change. The above reasoning might apply to a past time but I think it's
> bogus for a future time. If the TZ offset for a future time changes,
> it's likely because of a DST law change, and we are in Peter's
> what-time-is-the-appointment scenario. A TZ offset for a past time
> probably should not change, but if it does, it suggests a retroactive
> data correction. Surely you don't intend to prevent people from fixing
> bad data?

No, but I am mixing some different issues together. The original
question of this thread is what happens when the zone rules change for
an already-entered time. I contend the answer to that is a symptom of
the semantics of how it's treated, which boil down to whether a value
is stable relative to a specific zone, or to UTC. Other symptoms
include whether it accurately transports, can be retrieved in the same
form it was entered in, etc.

So the birth certificate argument is for past times, unlikely to have
zone rules change, but does need to be tagged with a specific time
zone so that it can be returned exactly the same way.

The appointment argument is for future times, more likely to have zone
rules change, and still needs to be tagged with a specific time zone.
That includes transport, which implies that it should never be exposed
in any other form.

Same semantics really, it's just that one problem is less likely to
happen in one of those situations.

If something like a birth date is found to be incorrect, it would have
to be corrected through official methods, which means some human
involvement. The only reasonable thing a database can do is keep it
exactly the same as entered until explicitly told otherwise; changing
it automatically is equivalent to corruption.

If the database is using zone rules that are out of date, and the
stamps are stored as local value and zone, only dynamic calculations
are affected. When the zone rules are updated, not changing the data
is always the correct approach.

I don't know if there have ever been retroactive changes to DST laws
we could look at, but I could easily see a change like that affecting
some things and not others. Individual organizations make their own
calls, state entities make varying decisions after gigantic reviews,
etc. It would not surprise me at all to see yearly permits
retroactively change, lifetime certificates stay the same because they
don't want to reprint stuff, except the modern computerized department
that doesn't need to reprint much of anything, etc. The correct
result is subjective, but since it's still a human call, you want to
default to not mangling the data.

People shouldn't be prevented from fixing bad data, but I don't see
how the database can possibly determine it *is* bad. It seems similar
to the server's clock being off while it's inserting data with NOW;
there's just nothing you can do to automatically repair that after you
fix the clock.

"Trevor Talbot" <quension(at)gmail(dot)com> writes:
> I don't know if there have ever been retroactive changes to DST laws
> we could look at, but I could easily see a change like that affecting
> some things and not others.

Even a politician would hardly be silly enough to make a retroactive
DST law change. On the other hand, it is *entirely* possible for a
computer system's DST rule files to be updated only after the effective
date of a law, and I think that's the case you'd want to design for.

Maybe, when you look at it that way, the past and future cases aren't so
different after all ...

regards, tom lane

Am Donnerstag, 11. Oktober 2007 schrieb Gregory Stark:
> Thinking of it as UTC is the wrong way to think about it. A birth occurred
> at a specific moment in time. You want to record that precise moment, not
> what it happened to show on the clock at the time. If the clock turns out
> to have been in the wrong timezone the birth isn't going to move.
>
> The use case for storing a local timestamp with a timezone attached is for
> things like appointments. If the time zone rules change you would want the
> appointment to move with them, not to stay at the same moment in time.

The difference here is that one occured in the past and one is planned for the
future. Appointments in the past will still stay at the same time even if
the time zone rules change afterwards.

The supercorrect way to handle this would likely be to introduce some sort of
time-zone rules changeset that describes "as of point in time X, the time
zone designation ABC changes in the following way", which would then fix up
all data items past point X in the database in some clever way. Obviously
this is quite a bit too much for us to manage.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/