On 10/10/07, Tom Lane <[EMAIL PROTECTED]> wrote: > "Trevor Talbot" <[EMAIL PROTECTED]> 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. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq