This doesn't work:

regression=# select '2006-07-13 09:20:00 EST5EDT'::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "2006-07-13 09:20:00 EST5EDT"

It never has worked in the past, of course, but I expected it to work in
HEAD seeing that the zic database includes EST5EDT as one of the defined
zone names (and pg_timezone_names shows it as available).

On investigation, the problem seems to lie with ParseDateTime(), which
is being quite finicky about what it will believe is a timezone name.
In particular it won't accept digits as part of the name. That can
probably be fixed but I'm wondering about the other more or less special
cases in the timezone name list, such as

America/Port-au-Prince
GB-Eire
GMT-0
Etc/GMT+8
W-SU

In particular it seems tough to accept GMT-0 as a timezone name without
breaking recognition of month-name dates such as 'Nov-09-2006'. Any
thoughts about appropriate heuristics? Should we just accept a few
cases where we don't recognize zic timezone names? (If so, we'd better
hack pg_timezone_names to not show those cases...)

Another problem here is case folding. The existing code smashes the
string to initcap format up to the '/' if any, but doesn't fold the part
after the '/', leaving us with a weird combination of case sensitivity
and insensitivity, not to mention another reason why EST5EDT won't work.
I'd like it to be entirely case insensitive, but am not sure how to make
the file lookup work that way. Thoughts?

regards, tom lane

Search Discussions

  • Martijn van Oosterhout at Oct 15, 2006 at 5:43 pm

    On Sat, Oct 14, 2006 at 07:42:18PM -0400, Tom Lane wrote:
    On investigation, the problem seems to lie with ParseDateTime(), which
    is being quite finicky about what it will believe is a timezone name.
    In particular it won't accept digits as part of the name. That can
    probably be fixed but I'm wondering about the other more or less special
    cases in the timezone name list, such as
    Last time I wrote some code with the zic database, I let it accept
    anything that started with a capital letter, which dropped the posix
    and leaps directories, the zone.tab file, etc.
    In particular it seems tough to accept GMT-0 as a timezone name without
    breaking recognition of month-name dates such as 'Nov-09-2006'. Any
    thoughts about appropriate heuristics? Should we just accept a few
    cases where we don't recognize zic timezone names? (If so, we'd better
    hack pg_timezone_names to not show those cases...)
    The only useful thing I can think of is the the timezones has to be the
    last part of the string. If it contains a slash, it's a timezone. Other
    than that...
    Another problem here is case folding. The existing code smashes the
    string to initcap format up to the '/' if any, but doesn't fold the part
    after the '/', leaving us with a weird combination of case sensitivity
    and insensitivity, not to mention another reason why EST5EDT won't work.
    I'd like it to be entirely case insensitive, but am not sure how to make
    the file lookup work that way. Thoughts?
    You can't make a file lookup that way. If you want that to work, you
    should preload a list of timezones and store the list internally.

    Another way to deal with start timezone names: we have a table for
    looking up "EST" and such, short names like GMT-0 could be added to
    that table...

    Have a nice day,
    --
    Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
    From each according to his ability. To each according to his ability to litigate.
  • Tom Lane at Oct 15, 2006 at 7:48 pm

    Martijn van Oosterhout writes:
    On Sat, Oct 14, 2006 at 07:42:18PM -0400, Tom Lane wrote:
    On investigation, the problem seems to lie with ParseDateTime(), which
    is being quite finicky about what it will believe is a timezone name.
    Last time I wrote some code with the zic database, I let it accept
    anything that started with a capital letter, which dropped the posix
    and leaps directories, the zone.tab file, etc.
    The trick is not so much where does the TZ name start, as where does it
    end. Also, we have to distinguish dates in the style Oct-15-2006 from
    TZ names ... where I believe the code also allows Oct/15/2006 and
    Oct.15.2006.

    We could probably distinguish the date case by the rules
    * exactly two separator characters, both alike;
    * only letters before the first separator, only digits after
    I'd like it to be entirely case insensitive, but am not sure how to make
    the file lookup work that way. Thoughts?
    You can't make a file lookup that way. If you want that to work, you
    should preload a list of timezones and store the list internally.
    That seems pretty expensive --- not too bad for Unix, in which we could
    let backends inherit the list from the postmaster, but in Windows each
    backend would have to compute its own list :-(

    We could implement our own case-insensitive file search atop a readdir()
    scan, but that might just be transferring the expense from one place to
    another. However, we do cache the results of TZ data loads, and most of
    the time a backend won't be referencing very many different TZ
    definitions over its lifespan, so it might work OK.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedOct 14, '06 at 11:42p
activeOct 15, '06 at 7:48p
posts3
users2
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase