Problem handling timezones
Hello all,
We are experiencing an error while trying to insert a row containing a date
string including timezone information:
acctvoip=> select version();
version
----------------------------------------------------------------
PostgreSQL 7.3 on sparc-sun-solaris2.6, compiled by GCC 2.95.3
(1 row)
acctvoip=> create table test (testdate timestamp with time zone);
CREATE TABLE
acctvoip=> insert into test values ('12:34:08.689 ART Fri Jan 10 2003');
ERROR: Bad timestamp external representation '12:34:08.689 ART Fri Jan 10
2003'
acctvoip=> insert into test values ('12:34:08.689 AWT Fri Jan 10 2003');
INSERT 56150645 1
So the problem arises when we try to use ART, which is the usual symbol for
Argentina Time (GMT-3). AWT, which is described in the docs as "unknown",
works just fine.
Our problem is that we have a lot of data sources which are already
configured to use ART as their TZ, so it would be a real pain to change
them all.
FWIW, I browsed the source tree and found that "art" is already defined in
datetime.c and appears to be included:
$ grep -in '"ART"' /opt/pgsql/src/postgresql-7.3/src/backend/utils/adt/datetime.c
119: {"art", TZ, NEG(12)}, /* Argentina Time */
But it doesn't work, as shown above. Any hints?
thanks in advance,
cl.
"Claudio Lapidus" <clapidus@ta.telecom.com.ar> writes:
FWIW, I browsed the source tree and found that "art" is already defined in
datetime.c and appears to be included:
$ grep -in '"ART"' /opt/pgsql/src/postgresql-7.3/src/backend/utils/adt/datetime.c
119: {"art", TZ, NEG(12)}, /* Argentina Time */
But it doesn't work, as shown above. Any hints?
Good catch. I think the problem is that ART is out of order in the table,
so it's not being found by the binary-search algorithm. Put it in
correct alphabetical order relative to the other entries, and I bet it
will work.
regards, tom lane
I said:
Good catch. I think the problem is that ART is out of order in the table,
so it's not being found by the binary-search algorithm. Put it in
correct alphabetical order relative to the other entries, and I bet it
will work.
Oh my, oh my ... turning over that rock revealed an embarrassingly large
number of bugs crawling about beneath it. Here is a patch to correct
the table if you want to apply a quick fix locally. This will be in
7.3.2 (along with some code to detect similar errors in future).
regards, tom lane
*** src/backend/utils/adt/datetime.c.orig Wed Nov 13 12:24:05 2002
--- src/backend/utils/adt/datetime.c Wed Jan 15 19:05:33 2003
***************
*** 69,82 ****
#define TOVAL(tp, v) ((tp)->value = ((v) < 0? NEG((-(v))/15): POS(v)/15)) /*
! * datetktbl holds date/time keywords. Note that this table must be strictly
! * ordered to allow an O(ln(N)) search algorithm.
*
! * The text field is not guaranteed to be NULL-terminated.
*
* To keep this table reasonably small, we divide the lexval for TZ and DTZ
* entries by 15 (so they are on 15 minute boundaries) and truncate the text
! * field at MAXTOKLEN characters.
* Formerly, we divided by 10 rather than 15 but there are a few time zones
* which are 30 or 45 minutes away from an even hour, most are on an hour
* boundary, and none on other boundaries.
--- 69,84 ----
#define TOVAL(tp, v) ((tp)->value = ((v) < 0? NEG((-(v))/15): POS(v)/15)) /*
! * datetktbl holds date/time keywords.
! *
! * Note that this table must be strictly alphabetically ordered to allow an
! * O(ln(N)) search algorithm to be used.
*
! * The text field is NOT guaranteed to be NULL-terminated.
*
* To keep this table reasonably small, we divide the lexval for TZ and DTZ
* entries by 15 (so they are on 15 minute boundaries) and truncate the text
! * field at TOKMAXLEN characters.
* Formerly, we divided by 10 rather than 15 but there are a few time zones
* which are 30 or 45 minutes away from an even hour, most are on an hour
* boundary, and none on other boundaries.
***************
*** 88,98 ****
static datetkn datetktbl[] = {
/* text, token, lexval */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
{"acsst", DTZ, POS(42)}, /* Cent. Australia */
{"acst", DTZ, NEG(16)}, /* Atlantic/Porto Acre */
{"act", TZ, NEG(20)}, /* Atlantic/Porto Acre */
{DA_D, ADBC, AD}, /* "ad" for years >= 0 */
- {"abstime", IGNORE_DTF, 0}, /* for pre-v6.1 "Invalid Abstime" */
{"adt", DTZ, NEG(12)}, /* Atlantic Daylight Time */
{"aesst", DTZ, POS(44)}, /* E. Australia */
{"aest", TZ, POS(40)}, /* Australia Eastern Std Time */
--- 90,100 ----
static datetkn datetktbl[] = {
/* text, token, lexval */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
+ {"abstime", IGNORE_DTF, 0}, /* for pre-v6.1 "Invalid Abstime" */
{"acsst", DTZ, POS(42)}, /* Cent. Australia */
{"acst", DTZ, NEG(16)}, /* Atlantic/Porto Acre */
{"act", TZ, NEG(20)}, /* Atlantic/Porto Acre */
{DA_D, ADBC, AD}, /* "ad" for years >= 0 */
{"adt", DTZ, NEG(12)}, /* Atlantic Daylight Time */
{"aesst", DTZ, POS(44)}, /* E. Australia */
{"aest", TZ, POS(40)}, /* Australia Eastern Std Time */
***************
*** 101,116 ****
{"akdt", DTZ, NEG(32)}, /* Alaska Daylight Time */
{"akst", DTZ, NEG(36)}, /* Alaska Standard Time */
{"allballs", RESERV, DTK_ZULU}, /* 00:00:00 */
- {"almt", TZ, POS(24)}, /* Almaty Time */
{"almst", TZ, POS(28)}, /* Almaty Savings Time */
{"am", AMPM, AM},
{"amst", DTZ, POS(20)}, /* Armenia Summer Time (Yerevan) */
- {"amt", TZ, POS(16)}, /* Armenia Time (Yerevan) */
#if 0
{"amst", DTZ, NEG(12)}, /* Porto Velho */
#endif
{"anast", DTZ, POS(52)}, /* Anadyr Summer Time (Russia) */
{"anat", TZ, POS(48)}, /* Anadyr Time (Russia) */
#if 0
aqtst
aqtt
--- 103,120 ----
{"akdt", DTZ, NEG(32)}, /* Alaska Daylight Time */
{"akst", DTZ, NEG(36)}, /* Alaska Standard Time */
{"allballs", RESERV, DTK_ZULU}, /* 00:00:00 */
{"almst", TZ, POS(28)}, /* Almaty Savings Time */
+ {"almt", TZ, POS(24)}, /* Almaty Time */
{"am", AMPM, AM},
{"amst", DTZ, POS(20)}, /* Armenia Summer Time (Yerevan) */
#if 0
{"amst", DTZ, NEG(12)}, /* Porto Velho */
#endif
+ {"amt", TZ, POS(16)}, /* Armenia Time (Yerevan) */
{"anast", DTZ, POS(52)}, /* Anadyr Summer Time (Russia) */
{"anat", TZ, POS(48)}, /* Anadyr Time (Russia) */
+ {"apr", MONTH, 4},
+ {"april", MONTH, 4},
#if 0
aqtst
aqtt
***************
*** 122,129 ****
ast /* Atlantic Standard Time, Arabia Standard
* Time, Acre Standard Time */
#endif
- {"apr", MONTH, 4},
- {"april", MONTH, 4},
{"ast", TZ, NEG(16)}, /* Atlantic Std Time (Canada) */
{"at", IGNORE_DTF, 0}, /* "at" (throwaway) */
{"aug", MONTH, 8},
--- 126,131 ----
***************
*** 181,192 ****
#endif
{"cot", TZ, NEG(20)}, /* Columbia Time */
{"cst", TZ, NEG(24)}, /* Central Standard Time */
#if 0
cvst
#endif
{"cvt", TZ, POS(28)}, /* Christmas Island Time (Indian Ocean) */
{"cxt", TZ, POS(28)}, /* Christmas Island Time (Indian Ocean) */
- {DCURRENT, RESERV, DTK_CURRENT}, /* "current" is always now */
{"d", UNITS, DTK_DAY}, /* "day of month" for ISO input */
{"davt", TZ, POS(28)}, /* Davis Time (Antarctica) */
{"ddut", TZ, POS(40)}, /* Dumont-d'Urville Time (Antarctica) */
--- 183,194 ----
#endif
{"cot", TZ, NEG(20)}, /* Columbia Time */
{"cst", TZ, NEG(24)}, /* Central Standard Time */
+ {DCURRENT, RESERV, DTK_CURRENT}, /* "current" is always now */
#if 0
cvst
#endif
{"cvt", TZ, POS(28)}, /* Christmas Island Time (Indian Ocean) */
{"cxt", TZ, POS(28)}, /* Christmas Island Time (Indian Ocean) */
{"d", UNITS, DTK_DAY}, /* "day of month" for ISO input */
{"davt", TZ, POS(28)}, /* Davis Time (Antarctica) */
{"ddut", TZ, POS(40)}, /* Dumont-d'Urville Time (Antarctica) */
***************
*** 414,421 ****
syot
#endif
{"t", ISOTIME, DTK_TIME}, /* Filler for ISO time fields */
- {"that", TZ, NEG(40)}, /* Tahiti Time */
{"tft", TZ, POS(20)}, /* Kerguelen Time */
{"thu", DOW, 4},
{"thur", DOW, 4},
{"thurs", DOW, 4},
--- 416,423 ----
syot
#endif
{"t", ISOTIME, DTK_TIME}, /* Filler for ISO time fields */
{"tft", TZ, POS(20)}, /* Kerguelen Time */
+ {"that", TZ, NEG(40)}, /* Tahiti Time */
{"thu", DOW, 4},
{"thur", DOW, 4},
{"thurs", DOW, 4},
***************
*** 516,524 ****
{DDAY, UNITS, DTK_DAY}, /* "day" relative */
{"days", UNITS, DTK_DAY}, /* "days" relative */
{"dec", UNITS, DTK_DECADE}, /* "decade" relative */
- {"decs", UNITS, DTK_DECADE}, /* "decades" relative */
{DDECADE, UNITS, DTK_DECADE}, /* "decade" relative */
{"decades", UNITS, DTK_DECADE}, /* "decades" relative */
{"h", UNITS, DTK_HOUR}, /* "hour" relative */
{DHOUR, UNITS, DTK_HOUR}, /* "hour" relative */
{"hours", UNITS, DTK_HOUR}, /* "hours" relative */
--- 518,526 ----
{DDAY, UNITS, DTK_DAY}, /* "day" relative */
{"days", UNITS, DTK_DAY}, /* "days" relative */
{"dec", UNITS, DTK_DECADE}, /* "decade" relative */
{DDECADE, UNITS, DTK_DECADE}, /* "decade" relative */
{"decades", UNITS, DTK_DECADE}, /* "decades" relative */
+ {"decs", UNITS, DTK_DECADE}, /* "decades" relative */
{"h", UNITS, DTK_HOUR}, /* "hour" relative */
{DHOUR, UNITS, DTK_HOUR}, /* "hour" relative */
{"hours", UNITS, DTK_HOUR}, /* "hours" relative */
***************
*** 534,540 ****
{"mils", UNITS, DTK_MILLENNIUM}, /* "millennia" relative */
{"min", UNITS, DTK_MINUTE}, /* "minute" relative */
{"mins", UNITS, DTK_MINUTE}, /* "minutes" relative */
- {"mins", UNITS, DTK_MINUTE}, /* "minutes" relative */
{DMINUTE, UNITS, DTK_MINUTE}, /* "minute" relative */
{"minutes", UNITS, DTK_MINUTE}, /* "minutes" relative */
{"mon", UNITS, DTK_MONTH}, /* "months" relative */
--- 536,541 ----
***************
*** 555,561 ****
{"seconds", UNITS, DTK_SECOND},
{"secs", UNITS, DTK_SECOND},
{DTIMEZONE, UNITS, DTK_TZ}, /* "timezone" time offset */
- {"timezone", UNITS, DTK_TZ}, /* "timezone" time offset */
{"timezone_h", UNITS, DTK_TZ_HOUR}, /* timezone hour units */
{"timezone_m", UNITS, DTK_TZ_MINUTE}, /* timezone minutes units */
{"undefined", RESERV, DTK_INVALID}, /* pre-v6.1 invalid time */
--- 556,561 ----