Thread: 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@momjian.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@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/ > 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@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
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
Magne Mæhre <Magne.Mahre@Sun.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.9000401@sun.com>, Magne Mæhre <Magne.Mahre@Sun.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.EE98.0025.0@wicourts.gov>, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote: >>>> On Wed, Oct 10, 2007 at 12:23 PM, in message > <470D0A90.9000401@sun.com>, Magne > Mæhre <Magne.Mahre@Sun.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.Grittner@wicourts.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@sss.pgh.pa.us> wrote: > "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)? 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.Mahre@sun.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@gmail.com> writes: > On 10/11/07, Magne M=E6hre <Magne.Mahre@sun.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@sss.pgh.pa.us> writes: > "Trevor Talbot" <quension@gmail.com> writes: >> On 10/11/07, Magne M=E6hre <Magne.Mahre@sun.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@enterprisedb.com> wrote: > "Tom Lane" <tgl@sss.pgh.pa.us> writes: > > > "Trevor Talbot" <quension@gmail.com> writes: > >> On 10/11/07, Magne Mæhre <Magne.Mahre@sun.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.
Magne Mæhre <Magne.Mahre@Sun.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@gmail.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@enterprisedb.com> wrote: > "Trevor Talbot" <quension@gmail.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@gmail.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@sss.pgh.pa.us> wrote: > "Trevor Talbot" <quension@gmail.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@gmail.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/