Re: Timezones (in 8.5?) - Mailing list pgsql-hackers
From | Andrew Gierth |
---|---|
Subject | Re: Timezones (in 8.5?) |
Date | |
Msg-id | 87iqd6b2vi.fsf@news-spur.riddles.org.uk Whole thread Raw |
In response to | Re: Timezones (in 8.5?) ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>) |
List | pgsql-hackers |
>>>>> "Kevin" == Kevin Grittner <Kevin.Grittner@wicourts.gov> writes: >>> For example, if you have a type T which incorporates a timestamp>>> and a timezone, what semantics does the T = T operatorhave? What>>> semantics apply if the definitions of timezones change?Kevin> I'd rather sort that out once and implementthe desiredKevin> semantics in the operators for a new type than to count onKevin> application programmers doingit consistently each time.Kevin> Wouldn't you? No, because the desired semantics are not the same for everyone, so even if you take just the two examples I gave above, you're already into combinatorial explosion with four different types needed. By keeping it as a composite value, you allow the app to define the semantics it needs.>>> What if you're storing times of events at specific places; in>>> that case you want to associate thetimezone with the _place_ not>>> the event (so that if the timezone rules change, moving the place>>> from one timezoneto another, you only have to change the place,>>> not all the events that refer to it).Kevin> I'm not sure I quitefollowed you there, but Hernán's exampleKevin> specifically called for storing 'Chile/Santiago', not a UTCKevin> offsetor something as easily changed as the 'CLT' or 'CLST'Kevin> time zone designations -- so it is tied to a place rathermoreKevin> closely than anything else. But those place definitions do occasionally change. For example, some US states can change timezone at county level; suppose a state that was previously all one timezone decides to change timezone or DST observance for all except a few counties that remain on the previous setting. So places within those counties will have to change timezone name from America/Somestate to America/Somestate/Oddcounty while places in the rest of the state stay with America/Somestate. The fact that geographic names are used for timezones doesn't mean that the timezone name applicable to a given place doesn't change; timezones in the database can split when rule changes happen that don't affect the full extent of the previous zone; this leads to two or more zones which have identical definitions up to some date, and different definitions after it. (Zones can only split, they can't merge, due to the necessity of keeping historical changes.) Kevin> I think that was part of his point -- that for civil time youKevin> care about what the clock on a typical business'swall at thatKevin> place will read on that date, regardless of what changes mightKevin> happen in time zone definitions. Right, but if timezone _boundaries_ change, this can't happen without some manual corrections. (If the timezone _rules_ change without changing the boundaries, then just updating the tzdata is enough if you designed the db correctly.) -- Andrew.
pgsql-hackers by date: