Re: Timezones (in 8.5?)
От | hgonzalez@gmail.com |
---|---|
Тема | Re: Timezones (in 8.5?) |
Дата | |
Msg-id | 000e0cd70ece3a08d90478be4580@google.com обсуждение исходный текст |
Ответ на | Re: Timezones (in 8.5?) (Andrew Gierth <andrew@tao11.riddles.org.uk>) |
Список | pgsql-hackers |
On Nov 19, 2009 1:18am, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:<br />> <br />> Right, but includingmore data in a single type is the wrong approach,<br />> since it complicates the semantics and interferes withnormalization.<br />> For example, if you have a type T which incorporates a timestamp and a<br />> timezone, whatsemantics does the T = T operator have? What semantics<br />> apply if the definitions of timezones change? <br /><br/>I dont get the thing about normalization, there's complete ortoghonality in my approach.<br />And when you say "complicatesthe semantic" I'd say "enrich the semantics" (and even<br />clarify it) so that it fits more neatly to the typicalusage of dates and times in real life.<br />For "datetimes with tz" the equality (and comparison) operator is nottrivial, roughly <br />in the same sense that date-time arithmetic is not trivial when one stops thinking of<br />datetimesas "physical time". So is life. Should the datetimes <br />'2010-07-27 9:30 Chile' and '2010-07-27 10:30 Argentine'(GMT+4 and GMT+3 respec)<br />be considered equal? It's arguable; but the ambiguity (just a matter of adoption)reflects<br />reality. We can discuss it and adopt some consistent criteria.<br /><br />> What if you're storing<br/>> times of events at specific places; in that case you want to associate<br />> the timezone with the _place_not the event (so that if the timezone<br />> rules change, moving the place from one timezone to another, youonly<br />> have to change the place, not all the events that refer to it).<br /><br />I'm not sure I undestand youhere. I'm claiming that timezone rules alterations<br />(zic files changes) should always be supported by the db implementation,without<br />needing of touching your data. And I believe that timestamps (i.e. physical times)<br />are inpractice almost never associated to timezone information. If you want to store<br />"the instant of last solar eclipse"you normally store the timestamp, a timezone<br />might only be useful for displaying (or as an adittional info,not really associated to the event)<br />A border case would be "store the instant of the death of John Lennon". Youmight<br />store the TZ here if you are interested in the civil time (so you can answer, for example,<br />¿how many rockstars died in morning/afternoon?). But then, again, you are here actually<br />storing a civil date (local date-timeplus TZ). The only problematic case i can envision<br />is to intend to store a physical time in the future withTZ, but frankly it is difficult to<br />think of this scenario (and even more difficult to think of needing to operatewith that<br />data as a whole; hence, in this case, to store the two fields separatadely makes sense).<br /><br />I'mbeing dense, and this might be a lost cause, but anyway, perhaps some day in the future<br />this might be of some use:<br/><br />I strongly believe that, if one could sample the real needings and usage of date-time types in <br />applicationsin this world, and taking apart types DATE (very frequent, but rather straightforward), <br />and TIME (notso relevant) and intervals (other issues here, much related to datetimes), the <br />overwhelming majority would fall( conceptually) into these three types:<br /><br />- TIMESTAMP (physical time - no TZ - no civil time implied)<br />-LOCAL DATETIME (civil time, no TZ)<br />- DATETIME (civil time with TZ => togheter with zic tables, implies a physicaltime)<br /><br />And of these three -I'd bet- the first is (conceptually) the most common, by a wide margin. <br/><br />As the name TIMESTAMP implies, it frequently records the moment of a event (in the DB corresponds<br /> frequentlyto the creation or alteration of a record, frequently via a "now()" default or such). <br />Examples: the timestampof messages in a mailing list, or issues in a bugtracker, or posts/articles in a blog/Cms.<br />Sometimes it ismodifiable by the user. Sometimes it is displayed (as a civil date, of course) according <br />to some TZ implied somewhereelse. It's normal that users with differnt TZ sees this event each <br />with its own TZ; and one is not directlyinterested on obtaining (say) an "inherent" civil datetime for the<br />event (for example one is not interestedin asking what posts where generated at midnight<br />acording to the localtime of the user that created it).<br/><br />The LOCAL DATETIME is only of use for civil date-times, when one is not directly interested in<br />asociateevents with real (physicial time) - this cannot be compared with a real time (it cant trigger alarms, eg)<br />Or,more rarely, when the TZ is implied somehere else (in the application, not it the DB server!).<br /><br />The DATETIMEis equivalent to the compound type {LOCAL_DATETIME,TZ}. Here the "civil date-time" is again<br />the primary conceptone deals with, but in a given place in the world (TZ), so it implies also (with the assistance of a zic table)<br/>a real time. This type is, IMHO, less frequent than the others. The typical use is for calendars or schedulers.<br/><br />One could, a propos Andrew's observation, consider a fourth type: TIMESTAMP WITH TZ. But it seems overkill:<br />except for ZIC changes, the correspondence with DATETIME is univocal (BTW, this is why in the Jodatime API<br/>-which does not deal with persistence- this concepts are strictly equivalent). Given this nearly-equivalence, andthat<br /> the needing of this type in real life is (IMO) almost null, I think that DATETIME is the one to survive.<br/><br />(One could even propose a fifht type: a TIMESTAMP WITH GMT OFFSET (roughly the ANSI proposal), which<br/>would be equivalent to have a timestamp AND a local datetime; this is more easy to deal with than timezones,<br/>but (as was discussed here before) is too limited (does not allow artithmetic) and is not orthogonal withthe real useful types.)<br /><br />Regarding implementation:<br /><br /> TIMESTAMP is straightforward, more or less thesame as today: stored as UTC, can be <br />input/output in ISO 8601 format (the client/server can use the offset theylike, internally it's translated to GMT+0)<br /><br />LOCAL_DATETIME also is straightforward, also stored as UTC (asin GMT+0) . BUT <br />- input/output in ISO 8601 format should not allow/produce GMT offset<br />- the similarity of implementationshould not leak upwards. This types are incompatible, cannot be compared, etc<br /><br />DATETIME is the difficultone, of course.<br /> - Equivalent to the pair {LOCAL_DATETIME,TX_id} (occupies more space)<br /> - Requires somecatalog table or something akin to codify consistently the timezones as numbers (included in pg_dump output?)<br /> -Requires new definitions for input/output (and deal with some ambiguities, particulary in DST transitions)<br /> - Requiressome semantic definitions (orderig, equality) <br /> - Some arithmetics (which involve convertion to physic time)may be expensive, might require some aggresive caching of time (zic) calculations.<br /><br />Global considerations:<br /> - Backward compatibility?<br /> - SQL spec compatibility? (is worsened?) (deprecate TIMESTAMP WITHTIMEZONE?)<br /> - implement conversion functions - castings (how strict?)<br /> - discuss/implement interval types/functions<br/> - interfaces (JDBC...)<br /><br />A bit of work, granted... (I might help)... but I bet that the mostdifficult work, by far, is to <br />reach an agreement :-)<br /><br />Thanks for reading.<br /><br />Hernán J. González
В списке pgsql-hackers по дате отправления:
Следующее
От: Itagaki TakahiroДата:
Сообщение: Re: Deleted WAL files held open by backends in Linux