Обсуждение: Units for storage of internal time zone offsets
We have "always" stored our time zone offsets in a compressed divide-by-ten form to allow storage in a single byte. But there are a few time zones ending at a 45 minute boundary, which "minutes divided by 10" can not represent. But "minutes divided by 15" can represent this and afaik all other time zones in existance. I have patches which will do this (with changes done by a perl script so the math must be right ;). Can anyone think of a counter-example time zone which does not fall on a 15 minute boundary? Or any other reason to not move to 15 minute quantization for these values? btw, the "45 minute cases" are Nepal and the Chatham Islands, and the "30 minute cases" are New Foundland, Canada and India, a bunch in Australia, and a few others. - Thomas
Thomas Lockhart <lockhart@fourpalms.org> writes: >> Is there a reason for the restriction to one byte? Offhand I don't >> recall that we store TZ offsets on disk at all... > Ah, we don't. Sorry I wasn't clear. This is only for the lookup table we > use to interpret time zones on *input*. It is contained in > src/backend/utils/adt/datetime.c Well, sheesh. Let's widen the byte field to int and store all the offsets in minutes, or even seconds. regards, tom lane
Thomas Lockhart <lockhart@fourpalms.org> writes: > We have "always" stored our time zone offsets in a compressed > divide-by-ten form to allow storage in a single byte. But there are a > few time zones ending at a 45 minute boundary, which "minutes divided by > 10" can not represent. Is there a reason for the restriction to one byte? Offhand I don't recall that we store TZ offsets on disk at all... > But "minutes divided by 15" can represent this and afaik all other time > zones in existance. I have patches which will do this (with changes done > by a perl script so the math must be right ;). Can anyone think of a > counter-example time zone which does not fall on a 15 minute boundary? I dug through the zic timezone database, and it seems that as of current practice, everyone is on hour, half-hour, or 15-minute GMT offsets, just as you note. But twenty-minute offsets were in use as recently as 1979 in some places (Line Islands), and if you want to go back before about 1950 you'd better be prepared to handle very weird offsets indeed. Given that we were just lecturing the glibc guys about access to pre-1970 timezone info, it doesn't sit very well with me to adopt a format that is designed only to support recent practice. My preference would be to store the GMT offset to the nearest minute, if we can afford the storage. What's the constraint exactly? regards, tom lane
> Well, sheesh. Let's widen the byte field to int and store all the > offsets in minutes, or even seconds. It doesn't really matter, since the table is used only internally, and only holds current accepted values for time zone offsets. The current scheme works. It might be useful to change the units, but how they are stored is an internal detail without much importance. Someone at some point decided that minimizing row width and table size was important (probably back in 1989 ;) and the table is structured with that in mind. - Thomas
> Is there a reason for the restriction to one byte? Offhand I don't > recall that we store TZ offsets on disk at all... Ah, we don't. Sorry I wasn't clear. This is only for the lookup table we use to interpret time zones on *input*. It is contained in src/backend/utils/adt/datetime.c And it has no long-term ramifications; it is entirely self-contained and does not result in information being stored on disk. This table does not have any concept of multiple values for a particular string time zone (e.g. "PST" or "EDT"). If/when we incorporate the zic stuff we will still need this lookup table, unless we develop an API to allow finding these within the zic database. - Thomas