Thread: Anyone using "POSIX" time zone offset capability?
While trying to clean up ParseDateTime so it works reliably with full
timezone names, I found out about a "feature" that so far as I can tell
has never been documented except in comments in datetime.c. The
datetime input code tries to recognize what it calls "POSIX time zones",
which are timezone abbreviations followed by an additional hour/minute
offset:
/* DecodePosixTimezone()
* Interpret string as a POSIX-compatible timezone:
* PST-hh:mm
* PST+h
* PST
* - thomas 2000-03-15
However this doesn't actually work in all cases:
regression=# select '12:34:00 PDT+00:30'::timetz;
timetz
----------------
12:34:00-07:30
(1 row)
regression=# select '12:34:00 PDT-00:30'::timetz;
ERROR: invalid input syntax for type time with time zone: "12:34:00 PDT-00:30"
(The behavior varies depending on which PG release you try it with, but
I can't find any that produce the expected result for a negative
fractional-hour offset.)
This syntax is ambiguous against some full timezone names present in the
zic database, such as "GMT+0", and it's also responsible for a number of
really ugly special cases in the datetime parser. In view of the fact
that it's never entirely worked and never been documented, I'm inclined
to take it out. Comments? Is anyone actually using this?
regards, tom lane
What about time zones like Tehran (GMT+3:30), Kabul (GMT+4:30), Katmandu
(GMT+5:45) and other non-cardinal-hour GMT offsets? Is this handled in
some *documented* way already?
--
Brandon Aiken
CS/IT Systems Engineer
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Monday, October 16, 2006 6:06 PM
To: pgsql-hackers@postgreSQL.org; pgsql-general@postgreSQL.org
Subject: [GENERAL] Anyone using "POSIX" time zone offset capability?
While trying to clean up ParseDateTime so it works reliably with full
timezone names, I found out about a "feature" that so far as I can tell
has never been documented except in comments in datetime.c. The
datetime input code tries to recognize what it calls "POSIX time zones",
which are timezone abbreviations followed by an additional hour/minute
offset:
/* DecodePosixTimezone()
* Interpret string as a POSIX-compatible timezone:
* PST-hh:mm
* PST+h
* PST
* - thomas 2000-03-15
However this doesn't actually work in all cases:
regression=# select '12:34:00 PDT+00:30'::timetz;
timetz
----------------
12:34:00-07:30
(1 row)
regression=# select '12:34:00 PDT-00:30'::timetz;
ERROR: invalid input syntax for type time with time zone: "12:34:00
PDT-00:30"
(The behavior varies depending on which PG release you try it with, but
I can't find any that produce the expected result for a negative
fractional-hour offset.)
This syntax is ambiguous against some full timezone names present in the
zic database, such as "GMT+0", and it's also responsible for a number of
really ugly special cases in the datetime parser. In view of the fact
that it's never entirely worked and never been documented, I'm inclined
to take it out. Comments? Is anyone actually using this?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
"Brandon Aiken" <BAiken@winemantech.com> writes:
> What about time zones like Tehran (GMT+3:30), Kabul (GMT+4:30), Katmandu
> (GMT+5:45) and other non-cardinal-hour GMT offsets? Is this handled in
> some *documented* way already?
Sure. This has worked since PG 7.2 or so:
regression=# select '12:34:00 IRT'::timetz;
timetz
----------------
12:34:00+03:30
(1 row)
Also you can just do
regression=# select '12:34:00 +03:30'::timetz;
timetz
----------------
12:34:00+03:30
(1 row)
regression=#
The weird thing about this allegedly-POSIX notation is the combination
of a symbolic name and a further offset from it. Back when we didn't
have customizable timezone abbreviations, maybe there would be some
point in making that work, but I don't see the point now. I'm not
entirely convinced that it really is a POSIX-sanctioned notation,
either --- the POSIX syntax the zic code knows about is different.
regards, tom lane
I wrote:
> ... I'm not entirely convinced that it really is a POSIX-sanctioned
> notation, either --- the POSIX syntax the zic code knows about is
> different.
Actually, I take that back: it is a subset of the same notation, but
the datetime.c code is misinterpreting the spec!
The POSIX timezone notation as understood by the zic code includes
the possibility of
zoneabbrev[+-]hh[:mm[:ss]]
but the meaning is that hh:mm:ss *is* the offset from GMT, and
zoneabbrev is being defined as the abbreviation for that offset.
What the datetime.c code is doing is trying to find the zoneabbrev
in a built-in timezone table, and then adding the two together.
This is simply wacko.
Given where the code stands now, I think the best solution is to
rip out DecodePosixTimezone and instead pass the syntax off to the
zic code (which can handle it via tzparse()). Since the datetime
input parser is ultimately only interested in the GMT offset value,
this would mean that the zoneabbrev part would become a noise word.
regards, tom lane
Hi, > The POSIX timezone notation as understood by the zic code includes > the possibility of > > zoneabbrev[+-]hh[:mm[:ss]] > > but the meaning is that hh:mm:ss *is* the offset from GMT, and > zoneabbrev is being defined as the abbreviation for that offset. > What the datetime.c code is doing is trying to find the zoneabbrev > in a built-in timezone table, and then adding the two together. > This is simply wacko. I think that if anyone has ever tried to use this notation they would have noticed this misinterpretation of the specs. > Given where the code stands now, I think the best solution is to > rip out DecodePosixTimezone and instead pass the syntax off to the > zic code (which can handle it via tzparse()). Since the datetime > input parser is ultimately only interested in the GMT offset value, > this would mean that the zoneabbrev part would become a noise word. Sounds like a good idea to me. Sander
"Sander Steffann" <s.steffann@computel.nl> writes:
>> What the datetime.c code is doing is trying to find the zoneabbrev
>> in a built-in timezone table, and then adding the two together.
>> This is simply wacko.
> I think that if anyone has ever tried to use this notation they would have
> noticed this misinterpretation of the specs.
Well, it'd work without surprise for the case of "GMT+-n", which is
undoubtedly the most common case ...
regards, tom lane
Hi, > "Sander Steffann" <s.steffann@computel.nl> writes: >>> What the datetime.c code is doing is trying to find the zoneabbrev >>> in a built-in timezone table, and then adding the two together. >>> This is simply wacko. > >> I think that if anyone has ever tried to use this notation they would >> have >> noticed this misinterpretation of the specs. > > Well, it'd work without surprise for the case of "GMT+-n", which is > undoubtedly the most common case ... Hmmmm. I hadn't thought of that, but then: with the changes you proposed they would still get what they expect. Even though that notation would not conform to the POSIX docs. Still seems like a good idea :) Sander
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> The weird thing about this allegedly-POSIX notation is the combination
Tom> of a symbolic name and a further offset from it.
AIUI, it is not a further offset but rather (mostly-)redundant data
specifying the exact offset from UTC¹ the text tz specifies. Having
both provides easy parsing both for humans (the text) and for code
(the number).
-JimC
[1] Of course POSIX time is not really offset from UTC, since POSIX
pretends there have been no leap seconds since 1970. As such
the timestamps are technically ambiguous as to whether the specify
real UTC-based time or POSIX time.... (Currently there is a
23-second difference between the two.)
--
James Cloos <cloos@jhcloos.com> OpenPGP: 0xED7DAEA6