Re: Australian timezone configure option - Mailing list pgsql-patches

> Hi,
>
> Being in Australia, it's always been a minor pain building the support
> for Australian timezone rules by defining USE_AUSTRALIAN_RULES to the
> compiler.  Not to mention the not inconsiderable pain involved in pawing
> through the code and documentation trying to work out why the timezones
> were wrong in the first place.

OK, this patch makes Australian_timezones a GUC option.  It can be set
anytime in psql.  The code uses a static variable to check if the GUC
setting has changed and adjust the C struct accordingly.  I have also
added code to allow the regression tests to pass even if postgresql.conf
has australian_timezones defined.

    test=>  select datetime('2001-01-01 00:00:00 EST');
           timestamp
    ------------------------
     2001-01-01 00:00:00-05
    (1 row)

    test=> set australian_timezones = true;
    SET VARIABLE
    test=>  select datetime('2001-01-01 00:00:00 EST');
           timestamp
    ------------------------
     2000-12-31 09:00:00-05
    (1 row)
    test=> reset all;
    RESET VARIABLE
    test=>  select datetime('2001-01-01 00:00:00 EST');
           timestamp
    ------------------------
     2001-01-01 00:00:00-05
    (1 row)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.64
diff -c -r1.64 datetime.c
*** src/backend/utils/adt/datetime.c    2001/05/03 22:53:07    1.64
--- src/backend/utils/adt/datetime.c    2001/06/12 03:52:10
***************
*** 22,27 ****
--- 22,28 ----
  #include <limits.h>

  #include "miscadmin.h"
+ #include "utils/guc.h"
  #include "utils/datetime.h"

  static int DecodeNumber(int flen, char *field,
***************
*** 35,40 ****
--- 36,42 ----
  static int    DecodeTimezone(char *str, int *tzp);
  static datetkn *datebsearch(char *key, datetkn *base, unsigned int nel);
  static int    DecodeDate(char *str, int fmask, int *tmask, struct tm * tm);
+ static void    CheckAustralianTimezones(int field);

  #define USE_DATE_CACHE 1
  #define ROUND_ALL 0
***************
*** 85,91 ****
   * entries by 10 and truncate the text field at MAXTOKLEN characters.
   * the text field is not guaranteed to be NULL-terminated.
   */
! static datetkn datetktbl[] = {
  /*        text            token    lexval */
      {EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
      {"acsst", DTZ, 63},            /* Cent. Australia */
--- 87,93 ----
   * entries by 10 and truncate the text field at MAXTOKLEN characters.
   * the text field is not guaranteed to be NULL-terminated.
   */
! datetkn datetktbl[] = {
  /*        text            token    lexval */
      {EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
      {"acsst", DTZ, 63},            /* Cent. Australia */
***************
*** 117,127 ****
      {"cdt", DTZ, NEG(30)},        /* Central Daylight Time */
      {"cet", TZ, 6},                /* Central European Time */
      {"cetdst", DTZ, 12},        /* Central European Dayl.Time */
! #if USE_AUSTRALIAN_RULES
!     {"cst", TZ, 63},            /* Australia Eastern Std Time */
! #else
!     {"cst", TZ, NEG(36)},        /* Central Standard Time */
! #endif
      {DCURRENT, RESERV, DTK_CURRENT},    /* "current" is always now */
      {"dec", MONTH, 12},
      {"december", MONTH, 12},
--- 119,125 ----
      {"cdt", DTZ, NEG(30)},        /* Central Daylight Time */
      {"cet", TZ, 6},                /* Central European Time */
      {"cetdst", DTZ, 12},        /* Central European Dayl.Time */
!     {"cst", TZ, NEG(36)},        /* Central Standard Time, may be Australian */
      {DCURRENT, RESERV, DTK_CURRENT},    /* "current" is always now */
      {"dec", MONTH, 12},
      {"december", MONTH, 12},
***************
*** 134,144 ****
      {"eet", TZ, 12},            /* East. Europe, USSR Zone 1 */
      {"eetdst", DTZ, 18},        /* Eastern Europe */
      {EPOCH, RESERV, DTK_EPOCH}, /* "epoch" reserved for system epoch time */
! #if USE_AUSTRALIAN_RULES
!     {"est", TZ, 60},            /* Australia Eastern Std Time */
! #else
!     {"est", TZ, NEG(30)},        /* Eastern Standard Time */
! #endif
      {"feb", MONTH, 2},
      {"february", MONTH, 2},
      {"fri", DOW, 5},
--- 132,138 ----
      {"eet", TZ, 12},            /* East. Europe, USSR Zone 1 */
      {"eetdst", DTZ, 18},        /* Eastern Europe */
      {EPOCH, RESERV, DTK_EPOCH}, /* "epoch" reserved for system epoch time */
!     {"est", TZ, NEG(30)},        /* Eastern Standard Time, may be Australian */
      {"feb", MONTH, 2},
      {"february", MONTH, 2},
      {"fri", DOW, 5},
***************
*** 199,209 ****
      {"pst", TZ, NEG(48)},        /* Pacific Standard Time */
      {"sadt", DTZ, 63},            /* S. Australian Dayl. Time */
      {"sast", TZ, 57},            /* South Australian Std Time */
! #if USE_AUSTRALIAN_RULES
!     {"sat", TZ, 57},
! #else
!     {"sat", DOW, 6},
! #endif
      {"saturday", DOW, 6},
      {"sep", MONTH, 9},
      {"sept", MONTH, 9},
--- 193,199 ----
      {"pst", TZ, NEG(48)},        /* Pacific Standard Time */
      {"sadt", DTZ, 63},            /* S. Australian Dayl. Time */
      {"sast", TZ, 57},            /* South Australian Std Time */
!     {"sat", DOW, 6},            /* may be changed to Australian */
      {"saturday", DOW, 6},
      {"sep", MONTH, 9},
      {"sept", MONTH, 9},
***************
*** 1618,1623 ****
--- 1608,1615 ----
      int            type;
      datetkn    *tp;

+     CheckAustralianTimezones(field);
+
  #if USE_DATE_CACHE
      if ((datecache[field] != NULL)
          && (strncmp(lowtoken, datecache[field]->token, TOKMAXLEN) == 0))
***************
*** 2455,2457 ****
--- 2447,2495 ----

      return 0;
  }    /* EncodeTimeSpan() */
+
+
+ static void    CheckAustralianTimezones(int field)
+ {
+     datetkn    *tp;
+     int prev_Australian_timezones = false;    /* structure preloaded as false */
+
+     if (Australian_timezones != prev_Australian_timezones)
+     {
+ #if USE_DATE_CACHE
+         datecache[field] = NULL;
+ #endif
+         /* CST */
+         tp = datebsearch("cst", datetktbl, szdatetktbl);
+         Assert(tp);
+         tp->type = TZ;
+         if (!Australian_timezones)
+             tp->value = NEG(36);    /* Central Standard Time */
+         else
+             tp->value = 63;            /* Australia Eastern Std Time */
+
+         /* EST */
+         tp = datebsearch("est", datetktbl, szdatetktbl);
+         Assert(tp);
+         tp->type = TZ;
+         if (!Australian_timezones)
+             tp->value = NEG(30);    /* Eastern Standard Time */
+         else
+             tp->value = 60;            /* Australia Eastern Std Time */
+
+         /* SAT */
+         tp = datebsearch("sat", datetktbl, szdatetktbl);
+         Assert(tp);
+         if (!Australian_timezones)
+         {
+             tp->type = DOW;
+             tp->value = 6;
+         }
+         else
+         {
+             tp->type = TZ;
+             tp->value = 57;
+         }
+         prev_Australian_timezones = Australian_timezones;
+     }
+ }
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.37
diff -c -r1.37 guc.c
*** src/backend/utils/misc/guc.c    2001/06/07 04:50:57    1.37
--- src/backend/utils/misc/guc.c    2001/06/12 03:52:11
***************
*** 71,76 ****
--- 71,78 ----

  bool        SQL_inheritance = true;

+ bool        Australian_timezones = false;
+
  #ifndef PG_KRB_SRVTAB
  #define PG_KRB_SRVTAB ""
  #endif
***************
*** 222,227 ****
--- 224,230 ----
      {"show_source_port", PGC_SIGHUP, &ShowPortNumber, false},

      {"sql_inheritance", PGC_USERSET, &SQL_inheritance, true},
+     {"australian_timezones", PGC_USERSET, &Australian_timezones, false},

      {"fixbtree", PGC_POSTMASTER, &FixBTree, true},

***************
*** 880,885 ****
--- 883,889 ----
          case PGC_BOOL:
              val = *((struct config_bool *) record)->variable ? "on" : "off";
              break;
+
          case PGC_INT:
              snprintf(buffer, 256, "%d", *((struct config_int *) record)->variable);
              val = buffer;
***************
*** 955,961 ****
              elog(FATAL, "out of memory");
      }
      else
! /* no equal sign in string */
      {
          *name = strdup(string);
          if (!*name)
--- 959,965 ----
              elog(FATAL, "out of memory");
      }
      else
!     /* no equal sign in string */
      {
          *name = strdup(string);
          if (!*name)
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.11
diff -c -r1.11 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    2001/05/07 23:32:55    1.11
--- src/backend/utils/misc/postgresql.conf.sample    2001/06/12 03:52:11
***************
*** 172,174 ****
--- 172,180 ----
  #trace_lock_oidmin = 16384
  #trace_lock_table = 0
  #endif
+
+
+ #
+ #    Lock Tracing
+ #
+ #australian_timezones = false
Index: src/include/utils/datetime.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/utils/datetime.h,v
retrieving revision 1.18
diff -c -r1.18 datetime.h
*** src/include/utils/datetime.h    2001/05/03 22:53:07    1.18
--- src/include/utils/datetime.h    2001/06/12 03:52:12
***************
*** 182,187 ****
--- 182,188 ----
      char        value;            /* this may be unsigned, alas */
  } datetkn;

+ extern datetkn datetktbl[];

  /* TMODULO()
   * Macro to replace modf(), which is broken on some platforms.
Index: src/include/utils/guc.h
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/include/utils/guc.h,v
retrieving revision 1.7
diff -c -r1.7 guc.h
*** src/include/utils/guc.h    2001/06/07 04:50:57    1.7
--- src/include/utils/guc.h    2001/06/12 03:52:12
***************
*** 68,72 ****
--- 68,73 ----
  extern bool Show_btree_build_stats;

  extern bool SQL_inheritance;
+ extern bool Australian_timezones;

  #endif     /* GUC_H */
Index: src/test/regress/expected/horology-no-DST-before-1970.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/horology-no-DST-before-1970.out,v
retrieving revision 1.12
diff -c -r1.12 horology-no-DST-before-1970.out
*** src/test/regress/expected/horology-no-DST-before-1970.out    2001/04/06 05:50:25    1.12
--- src/test/regress/expected/horology-no-DST-before-1970.out    2001/06/12 03:52:16
***************
*** 4,9 ****
--- 4,11 ----
  --
  -- date, time arithmetic
  --
+ -- needed so tests pass
+ SET australian_timezones = 'off';
  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
           Date + Time
  ------------------------------
Index: src/test/regress/expected/horology-solaris-1947.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/horology-solaris-1947.out,v
retrieving revision 1.10
diff -c -r1.10 horology-solaris-1947.out
*** src/test/regress/expected/horology-solaris-1947.out    2001/04/06 05:50:25    1.10
--- src/test/regress/expected/horology-solaris-1947.out    2001/06/12 03:52:17
***************
*** 4,9 ****
--- 4,11 ----
  --
  -- date, time arithmetic
  --
+ -- needed so tests pass
+ SET australian_timezones = 'off';
  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
           Date + Time
  ------------------------------
Index: src/test/regress/expected/horology.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/horology.out,v
retrieving revision 1.23
diff -c -r1.23 horology.out
*** src/test/regress/expected/horology.out    2001/04/06 05:50:25    1.23
--- src/test/regress/expected/horology.out    2001/06/12 03:52:19
***************
*** 4,9 ****
--- 4,11 ----
  --
  -- date, time arithmetic
  --
+ -- needed so tests pass
+ SET australian_timezones = 'off';
  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";
           Date + Time
  ------------------------------
Index: src/test/regress/expected/timestamp.out
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/expected/timestamp.out,v
retrieving revision 1.12
diff -c -r1.12 timestamp.out
*** src/test/regress/expected/timestamp.out    2001/05/03 19:00:37    1.12
--- src/test/regress/expected/timestamp.out    2001/06/12 03:52:19
***************
*** 4,9 ****
--- 4,11 ----
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26
+ -- needed so tests pass
+ SET australian_timezones = 'off';
  SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
   True
  ------
Index: src/test/regress/sql/horology.sql
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/sql/horology.sql,v
retrieving revision 1.14
diff -c -r1.14 horology.sql
*** src/test/regress/sql/horology.sql    2001/04/06 05:50:29    1.14
--- src/test/regress/sql/horology.sql    2001/06/12 03:52:19
***************
*** 1,10 ****
  --
  -- HOROLOGY
  --
-
  --
  -- date, time arithmetic
  --

  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";

--- 1,11 ----
  --
  -- HOROLOGY
  --
  --
  -- date, time arithmetic
  --
+ -- needed so tests pass
+ SET australian_timezones = 'off';

  SELECT date '1981-02-03' + time '04:05:06' AS "Date + Time";

Index: src/test/regress/sql/timestamp.sql
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/test/regress/sql/timestamp.sql,v
retrieving revision 1.7
diff -c -r1.7 timestamp.sql
*** src/test/regress/sql/timestamp.sql    2000/11/25 05:00:33    1.7
--- src/test/regress/sql/timestamp.sql    2001/06/12 03:52:20
***************
*** 1,10 ****
  --
  -- DATETIME
  --
-
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26

  SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
  SELECT (timestamp 'today' = (timestamp 'tomorrow' - interval '1 day')) as "True";
--- 1,11 ----
  --
  -- DATETIME
  --
  -- Shorthand values
  -- Not directly usable for regression testing since these are not constants.
  -- So, just try to test parser and hope for the best - thomas 97/04/26
+ -- needed so tests pass
+ SET australian_timezones = 'off';

  SELECT (timestamp 'today' = (timestamp 'yesterday' + interval '1 day')) as "True";
  SELECT (timestamp 'today' = (timestamp 'tomorrow' - interval '1 day')) as "True";