Re: Proposal for better support of time-varying timezone abbreviations
От | Gavin Flower |
---|---|
Тема | Re: Proposal for better support of time-varying timezone abbreviations |
Дата | |
Msg-id | 5431C662.4010402@archidevsys.co.nz обсуждение исходный текст |
Ответ на | Proposal for better support of time-varying timezone abbreviations (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Proposal for better support of time-varying timezone abbreviations
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
<div class="moz-cite-prefix">On 06/10/14 10:33, Tom Lane wrote:<br /></div><blockquote cite="mid:27083.1412544800@sss.pgh.pa.us"type="cite"><pre wrap="">I got interested in the problem discussed in <a class="moz-txt-link-freetext" href="http://www.postgresql.org/message-id/20714.1412456604@sss.pgh.pa.us">http://www.postgresql.org/message-id/20714.1412456604@sss.pgh.pa.us</a> to wit: </pre><blockquote type="cite"><pre wrap="">It's becoming clear to me that our existing design whereby zone abbreviations represent fixed GMT offsets isn't really good enough. I've been wondering whether we could change things so that, for instance, "EDT" means "daylight time according to America/New_York" and the system would consult the zic database to find out what the prevailing GMT offset was in that zone on that date. This would be a lot more robust in the face of the kind of foolishness we now see actually goes on. </pre></blockquote><pre wrap=""> Here is a fairly detailed design sketch for a solution: 1. Allow tznames entries to consist of an abbreviation and the name of a zic timezone, for example MSK Europe/Moscow instead of the current scheme whereby an abbreviation is defined by a daylight-savings flag and a numeric GMT offset. When an abbreviation is defined this way, the implied offset and DST flag are looked up dynamically as described below. (In my message quoted above, I'd imagined that we'd write a DST flag and a zone name, but it turns out this does not work because there are cases where the DST property has changed over time. Yes, really. So this design mandates that we derive the DST flag by looking into the zic timezone data.) Note that we'll still allow the old style of entries, and indeed prefer that way for cases where an abbreviation has never changed meaning, because: * We need that anyway for forwards compatibility of existing custom abbreviations files. * It's a lot cheaper to interpret a fixed-meaning zone abbreviation using the existing logic than to do it as I propose here, so we shouldn't spend the extra cycles unless necessary. * Converting every one of the existing abbreviation-file entries would be really tedious, so I don't want to do it where not necessary. Also note that this doesn't touch the aspect of the existing design whereby there are multiple potential abbreviations files. We still have the problem that the same abbreviation can be in use in different timezones, so we have to let users configure which zone they mean by a given abbreviation. 2. To interpret such an abbreviation in the context of timestamptz input, look up the referenced zic timezone, and use the meaning of the abbreviation that prevailed at or most recently before the local time indicated by the rest of the timestamptz string. If the abbreviation was never used before that time in the given zone, use its earliest later interpretation; or if it was never used at all (ie bad configuration file) throw error. Note that this is different from what happens if you give the underlying zone name directly. It's always been the case that you could say, for instance, "EST" to force interpretation of a datetime as standard time even when DST is in force, or "EDT" to force the opposite interpretation, and this definition preserves that behavior. 3. In the context of timetz input, we only have a time of day not a full datetime to look at, so it's not entirely clear what to do. We could throw an error, but that would result in rejecting some inputs currently considered valid. Perhaps we don't really care, since we consider timetz a deprecated type anyway. If that doesn't seem OK, we could assume today's date and the given time-of-day and look up the abbreviation's meaning as described above. This would mean that the meaning of, say, '15:00 MSK'::timetz would change over time --- but that happens now, whenever we change the contents of the abbreviations file entry for MSK, so maybe this isn't as horrid as it sounds. 4. I've eyeballed the relevant code a bit, and it seems that the only implementation aspect that isn't perfectly straightforward is figuring out how to cram a zic timezone reference into a datetkn table entry. I suggest that before tackling this feature proper, we bring struct datetkn into the 21st century by widening it from 12 to 16 bytes, along the lines of typedef struct { char token[TOKMAXLEN + 1]; /* now always null-terminated */ char type; int32 value; } datetkn; and getting rid of all of the very crufty code that deals with non-null-terminated token strings and cramming values that don't really fit into a char-sized field into "value". (We might save more code bytes that way than we spend on the wider token-table entries :-( ... and we'll certainly make the code less ugly.) Having done that, the "value" can be large enough to be an index into additional storage appended to a TimeZoneAbbrevTable. I imagine it pointing at a struct like this: struct DynamicTimeZoneAbbrev { const pg_tz *tz; /* zic timezone, or NULL if not yet looked up */ char name[1]; /* zone name (variablelength string) }; We'd resolve the timezone name into a pg_tz pointer only upon first use of a dynamic abbreviation, since we don't want to force loading of every zone referenced in the configuration file at startup; many sessions wouldn't ever use them. (I also considered just allowing struct datetkn to contain a pointer; but adding a union would make initialization of constant datetkn arrays more notationally painful, and perhaps impossible with older C compilers.) 5. It's worth debating whether we should back-patch such a change. It certainly is a feature addition, and as such not something we'd normally consider back-patching, but: * Those time-varying zone abbreviations are out there whether we like it or not. As Bruce noted in the other thread, this is going to be a pain point for a lot of people, particularly in Russia. * Our maintenance processes for the timezone data files assume that we can back-patch the same change into all active branches. It'll be a lot more tedious and error-prone if we can only use this feature in the most recent branches. So I'm inclined to propose not merely doing this, but back-patching into all supported branches. I can see that there might be consensus against that though. Thoughts, objections, better ideas? regards, tom lane </pre></blockquote> What I am going to discuss may be way too complicated to implement (or impractical for other reasons!),Ibut I feel that I should at least mention it - because it might (does?) address real problems (I've been bittenby this kind of problem in the past).<br /><br /> In a totally different context relating to insurance quotes, I deviseda scheme to use both an <i>effective_date</i> & an <i>as_at_date</i>. How these concepts might be implementedin pg, in this instance, is likely to be very different from what I did originally.<br /><br /> I have not checked,but I suspect that pg probably already uses an <i>effective_date</i> to control when changes to daylight saving date/time'scome into affect (such as a change in the date that the transition to daylight saving takes effect). If not,then maybe this should be considered. This could also be used, if it was desired to use the appropriate abbreviationand offset valid at date/time where it was different to that defined at the current date/time.<br /><br /> Theuse of an <i>as_at_date</i> is far more problematic. The idea relates to how existing date/times should be treated withrespect to the date/time that a pg database is updated with new time zone data files. In the simplest form: there wouldbe a function in pg that would return the date/time a new time zone data file was entered into the system, so that applicationsoftware can manually correct when the stored GMT date/time was stored incorrectly because the wring GMT offsetwas used due to the updated time zone data files not being in place. Alternatively, pg could offer to do the correctionin a one-off action at the time the new zone data files were updated.<br /><br /><br /> Cheers,<br /> Gavin<br/>
В списке pgsql-hackers по дате отправления:
Следующее
От: Tom LaneДата:
Сообщение: Re: Proposal for better support of time-varying timezone abbreviations