Обсуждение: BUG #14337: "HDT" time zone is not recognized by PostreSQL 9.5
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMzNwpMb2dnZWQgYnk6ICAg ICAgICAgIFBhdm9sIFN6ZWdoZW8KRW1haWwgYWRkcmVzczogICAgICBwc3pl Z2hlb0BpdG5uZXR3b3Jrcy5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiA5LjUu NApPcGVyYXRpbmcgc3lzdGVtOiAgIFdpbmRvd3MgMTAKRGVzY3JpcHRpb246 ICAgICAgICAKCiJIRFQiIHRpbWUgem9uZSBpcyBub3QgcmVjb2duaXplZCBi eSBQb3N0cmVTUUwgOS41DQoNCk5laXRoZXIgYXJlIHRoZSBtb3JlIG1vZGVy biBIQURUIGFuZCBIQVNUIHRpbWUgem9uZXMgcmVjb2duaXplZCBieSBQb3N0 cmVTUUwKOS41DQoNClNvLCB0aGUgb25seSBIYXdhaWlhbiB0aW1lem9uZSBy ZWNvZ25pemVkIGJ5IFBvc3RyZVNRTCA5LjUgaXMgSFNULCB0aHVzIHRoZQpm b2xsb3dpbmcgd291bGQgcnVuIGZpbmU6DQpTRUxFQ1QgVElNRVNUQU1QICcy MDEzLTA4LTEzIDAwOjAwOjAwJyBBVCBUSU1FIFpPTkUgJ0hTVCc7DQoNCldo aWxlIGFsbCB0aGVzZSB3b3VsZCBmYWlsOg0KDQpTRUxFQ1QgVElNRVNUQU1Q ICcyMDEzLTA4LTEzIDAwOjAwOjAwJyBBVCBUSU1FIFpPTkUgJ0hEVCc7DQoN ClNFTEVDVCBUSU1FU1RBTVAgJzIwMTMtMDgtMTMgMDA6MDA6MDAnIEFUIFRJ TUUgWk9ORSAnSEFEVCc7DQoNClNFTEVDVCBUSU1FU1RBTVAgJzIwMTMtMDgt MTMgMDA6MDA6MDAnIEFUIFRJTUUgWk9ORSAnSEFTVCc7DQoNCmUuZy46DQpT RUxFQ1QgVElNRVNUQU1QICcyMDEzLTA4LTEzIDAwOjAwOjAwJyBBVCBUSU1F IFpPTkUgJ0hEVCc7DQovKg0KRXhhbXBsZSBvdXRwdXQ6DQogRVJST1I6ICB0 aW1lIHpvbmUgIkhEVCIgbm90IHJlY29nbml6ZWQNCioqKioqKioqKiogRXJy b3IgKioqKioqKioqKg0KDQpFUlJPUjogdGltZSB6b25lICJIRFQiIG5vdCBy ZWNvZ25pemVkDQpTUUwgc3RhdGU6IDIyMDIzDQoqLw0KCgo=
On Sat, Sep 24, 2016 at 7:52 AM, <pszegheo@itnnetworks.com> wrote: > The following bug has been logged on the website: > > Bug reference: 14337 > Logged by: Pavol Szegheo > Email address: pszegheo@itnnetworks.com > PostgreSQL version: 9.5.4 > Operating system: Windows 10 > Description: > > "HDT" time zone is not recognized by PostreSQL 9.5 > > Neither are the more modern HADT and HAST time zones recognized by PostreSQL > 9.5 > > So, the only Hawaiian timezone recognized by PostreSQL 9.5 is HST, thus the > following would run fine: > SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HST'; > > While all these would fail: > > SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HDT'; > > SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HADT'; > > SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HAST'; > > e.g.: > SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HDT'; > /* > Example output: > ERROR: time zone "HDT" not recognized > ********** Error ********** > > ERROR: time zone "HDT" not recognized > SQL state: 22023 > */ Hawaii doesn't do daylight savings. BTW Most people would advise you to use 'Pacific/Honolulu' instead. -- Thomas Munro http://www.enterprisedb.com
pszegheo@itnnetworks.com writes:
> "HDT" time zone is not recognized by PostreSQL 9.5
> Neither are the more modern HADT and HAST time zones recognized by PostreSQL
> 9.5
The IANA timezone database does not think that either HAST or HDST have
ever been in common use, and HDT hasn't been in use since 1945:
# Zone NAME GMTOFF RULES FORMAT [UNTIL]
Zone Pacific/Honolulu -10:31:26 - LMT 1896 Jan 13 12:00
-10:30 - HST 1933 Apr 30 2:00
-10:30 1:00 HDT 1933 May 21 12:00
-10:30 - HST 1942 Feb 9 2:00
-10:30 1:00 HDT 1945 Sep 30 2:00
-10:30 - HST 1947 Jun 8 2:00
-10:00 - HST
We generally avoid including nonstandard abbreviations in PG's default
timezone abbreviations list, since they seem more likely to allow garbage
data to be entered than to be useful. Of course, you can set up your own
abbreviation list if you wish to use specific additional abbreviations.
See
https://www.postgresql.org/docs/9.5/static/datetime-config-files.html
regards, tom lane
On Sat, Sep 24, 2016 at 8:48 AM, Thomas Munro <thomas.munro@enterprisedb.com> wrote: > On Sat, Sep 24, 2016 at 7:52 AM, <pszegheo@itnnetworks.com> wrote: >> The following bug has been logged on the website: >> >> Bug reference: 14337 >> Logged by: Pavol Szegheo >> Email address: pszegheo@itnnetworks.com >> PostgreSQL version: 9.5.4 >> Operating system: Windows 10 >> Description: >> >> "HDT" time zone is not recognized by PostreSQL 9.5 >> >> Neither are the more modern HADT and HAST time zones recognized by PostreSQL >> 9.5 >> >> So, the only Hawaiian timezone recognized by PostreSQL 9.5 is HST, thus the >> following would run fine: >> SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HST'; >> >> While all these would fail: >> >> SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HDT'; >> >> SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HADT'; >> >> SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HAST'; >> >> e.g.: >> SELECT TIMESTAMP '2013-08-13 00:00:00' AT TIME ZONE 'HDT'; >> /* >> Example output: >> ERROR: time zone "HDT" not recognized >> ********** Error ********** >> >> ERROR: time zone "HDT" not recognized >> SQL state: 22023 >> */ > > Hawaii doesn't do daylight savings. > > BTW Most people would advise you to use 'Pacific/Honolulu' instead. Hmm. src/timezone/data/northamerica does reference HDT though: # From Paul Eggert (2015-04-17): # HST and HDT are standardized abbreviations for Hawaii-Aleutian # standard and daylight times. See section 9.47 (p 234) of the # U.S. Government Printing Office Style Manual (2008) # http://www.gpo.gov/fdsys/pkg/GPO-STYLEMANUAL-2008/pdf/GPO-STYLEMANUAL-2008.pdf ... and ... # From Arthur David Olson (2011-01-19): # The following is from "Laws of the Territory of Hawaii Passed by the # Seventeenth Legislature: Regular Session 1933," available (as of # 2011-01-19) at American University's Pence Law Library. Page 85: "Act # 90...At 2 o'clock ante meridian of the last Sunday in April of each # year, the standard time of this Territory shall be advanced one # hour...This Act shall take effect upon its approval. Approved this 26th # day of April, A. D. 1933. LAWRENCE M JUDD, Governor of the Territory of # Hawaii." Page 172: "Act 163...Act 90 of the Session Laws of 1933 is # hereby repealed...This Act shall take effect upon its approval, upon # which date the standard time of this Territory shall be restored to # that existing immediately prior to the taking effect of said Act 90. # Approved this 21st day of May, A. D. 1933. LAWRENCE M. JUDD, Governor # of the Territory of Hawaii." # # Note that 1933-05-21 was a Sunday. # We're left to guess the time of day when Act 163 was approved; guess noon. # Zone NAME GMTOFF RULES FORMAT [UNTIL] Zone Pacific/Honolulu -10:31:26 - LMT 1896 Jan 13 12:00 -10:30 - HST 1933 Apr 30 2:00 -10:30 1:00 HDT 1933 May 21 12:00 -10:30 - HST 1942 Feb 9 2:00 -10:30 1:00 HDT 1945 Sep 30 2:00 -10:30 - HST 1947 Jun 8 2:00 -10:00 - HST Link Pacific/Honolulu Pacific/Johnston But I guess it doesn't work when you specify HDT explicitly because it's missing from this bit: # From Arthur David Olson, 2005-12-19 # We generate the files specified below to guard against old files with # obsolete information being left in the time zone binary directory. # We limit the list to names that have appeared in previous versions of # this time zone package. # We do these as separate Zones rather than as Links to avoid problems if # a particular place changes whether it observes DST. # We put these specifications here in the northamerica file both to # increase the chances that they'll actually get compiled and to # avoid the need to duplicate the US rules in another file. # Zone NAME GMTOFF RULES FORMAT [UNTIL] Zone EST -5:00 - EST Zone MST -7:00 - MST Zone HST -10:00 - HST Zone EST5EDT -5:00 US E%sT Zone CST6CDT -6:00 US C%sT Zone MST7MDT -7:00 US M%sT Zone PST8PDT -8:00 US P%sT Perhaps you'd need to ask about this over here? https://www.iana.org/time-zones -- Thomas Munro http://www.enterprisedb.com
Thomas Munro <thomas.munro@enterprisedb.com> writes:
>> On Sat, Sep 24, 2016 at 7:52 AM, <pszegheo@itnnetworks.com> wrote:
>>> "HDT" time zone is not recognized by PostreSQL 9.5
> Hmm. src/timezone/data/northamerica does reference HDT though:
Actually, some further digging finds this:
Zone America/Adak 12:13:21 - LMT 1867 Oct 18
-11:46:38 - LMT 1900 Aug 20 12:00
-11:00 - NST 1942
-11:00 US N%sT 1946
-11:00 - NST 1967 Apr
-11:00 - BST 1969
-11:00 US B%sT 1983 Oct 30 2:00
-10:00 US AH%sT 1983 Nov 30
-10:00 US H%sT
So this zone really is using HDT to refer to UTC-9. But it's someplace
in the Aleutians, not Hawaii, and Wikipedia says the total population of
Adak is 326. So I remain dubious that we want to add HDT to the standard
abbreviation list --- there are mighty few people for whom it wouldn't be
a typo.
I see we do list it in the tznames/America.txt data file:
HADT -32400 D # Hawaii-Aleutian Daylight Time (obsolete abbreviation)
# (America/Adak)
HAST -36000 # Hawaii-Aleutian Standard Time (obsolete abbreviation)
# (America/Adak)
HDT -32400 D # Hawaiian-Aleutian Daylight Time
# (America/Adak)
and that seems like the appropriate treatment.
regards, tom lane