Not quite there on timezone names in timestamp input

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Joachim Wieland <joe(at)mcknight(dot)de>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Not quite there on timezone names in timestamp input
Date: 2006-10-14 23:42:18
Message-ID: 13410.1160869338@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-10-14 23:48:06 Re: Problems building 8.2beta1 on macos G5 xserve
Previous Message Sean Davis 2006-10-14 22:47:45 Problems building 8.2beta1 on macos G5 xserve