Timezone database changes

On 10/8/07, Bruce Momjian <bruce@momjian.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@momjian.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@gmx.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.l993mOG15547@momjian.us>, Bruce Momjian <bruce@momjian.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.6080502@sun.com>, Magne
Mï¿œhre <Magne.Mahre@Sun.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@svana.org> http://svana.org/kleptog/

Trevor Talbot wrote:

I wrote:

On 10/8/07, Bruce Momjian <bruce@momjian.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@momjian.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.l99HB9x15798@momjian.us>, Bruce Momjian <bruce@momjian.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@gmx.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@gmx.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@highrise.ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk <aidan@highrise.ca> writes:

* Peter Eisentraut <peter_e@gmx.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@momjian.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@sss.pgh.pa.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@gmail.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