Re: Problem handling timezones - Mailing list pgsql-general

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 ----