Re: Australian timezone configure option

On Mon, Jun 11, 2001 at 11:53:59PM -0400, Bruce Momjian wrote:
> > 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.

Your patch had one reject against 7.1.2 (a single blank line in guc.c),
but it works for me once that was fixed.

Below is the patch against 7.1.2 I generated from your cvs patch.

I guess some documentation would be nice...

Thanks for your effort!

Cheers,

Chris,
OnTheNet

diff -ru postgresql-7.1.2.orig/src/backend/utils/adt/datetime.c postgresql-7.1.2/src/backend/utils/adt/datetime.c
--- postgresql-7.1.2.orig/src/backend/utils/adt/datetime.c Fri May 4 08:53:07 2001
+++ postgresql-7.1.2/src/backend/utils/adt/datetime.c Tue Jun 12 14:14:38 2001
@@ -22,6 +22,7 @@
#include <limits.h>

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

static int DecodeNumber(int flen, char *field,
@@ -35,6 +36,7 @@
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,7 +87,7 @@
* entries by 10 and truncate the text field at MAXTOKLEN characters.
* the text field is not guaranteed to be NULL-terminated.
*/
-static datetkn datetktbl[] = {
+datetkn datetktbl[] = {
/* text token lexval */
{EARLY, RESERV, DTK_EARLY}, /* "-infinity" reserved for "early time" */
{"acsst", DTZ, 63}, /* Cent. Australia */
@@ -117,11 +119,7 @@
{"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
+ {"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,11 +132,7 @@
{"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
+ {"est", TZ, NEG(30)}, /* Eastern Standard Time, may be Australian */
{"feb", MONTH, 2},
{"february", MONTH, 2},
{"fri", DOW, 5},
@@ -199,11 +193,7 @@
{"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
+ {"sat", DOW, 6}, /* may be changed to Australian */
{"saturday", DOW, 6},
{"sep", MONTH, 9},
{"sept", MONTH, 9},
@@ -1618,6 +1608,8 @@
int type;
datetkn *tp;

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

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;
+ }
+}
diff -ru postgresql-7.1.2.orig/src/backend/utils/misc/guc.c postgresql-7.1.2/src/backend/utils/misc/guc.c
--- postgresql-7.1.2.orig/src/backend/utils/misc/guc.c Fri Mar 23 04:41:47 2001
+++ postgresql-7.1.2/src/backend/utils/misc/guc.c Tue Jun 12 14:14:38 2001
@@ -70,6 +70,8 @@

bool SQL_inheritance = true;

+bool Australian_timezones = false;
+
#ifndef PG_KRB_SRVTAB
#define PG_KRB_SRVTAB ""
#endif
@@ -220,6 +222,7 @@
{"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},

@@ -856,7 +859,7 @@
elog(FATAL, "out of memory");
}
else
-/* no equal sign in string */
+ /* no equal sign in string */
{
*name = strdup(string);
if (!*name)
diff -ru postgresql-7.1.2.orig/src/backend/utils/misc/postgresql.conf.sample postgresql-7.1.2/src/backend/utils/misc/postgresql.conf.sample
--- postgresql-7.1.2.orig/src/backend/utils/misc/postgresql.conf.sample Fri Mar 16 16:44:33 2001
+++ postgresql-7.1.2/src/backend/utils/misc/postgresql.conf.sample Tue Jun 12 14:14:38 2001
@@ -172,3 +172,9 @@
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#endif
+
+
+#
+# Lock Tracing
+#
+#australian_timezones = false
diff -ru postgresql-7.1.2.orig/src/include/utils/datetime.h postgresql-7.1.2/src/include/utils/datetime.h
--- postgresql-7.1.2.orig/src/include/utils/datetime.h Fri May 4 08:53:07 2001
+++ postgresql-7.1.2/src/include/utils/datetime.h Tue Jun 12 14:14:38 2001
@@ -182,6 +182,7 @@
char value; /* this may be unsigned, alas */
} datetkn;

+extern datetkn datetktbl[];

/* TMODULO()
* Macro to replace modf(), which is broken on some platforms.
diff -ru postgresql-7.1.2.orig/src/include/utils/guc.h postgresql-7.1.2/src/include/utils/guc.h
--- postgresql-7.1.2.orig/src/include/utils/guc.h Thu Mar 22 15:01:12 2001
+++ postgresql-7.1.2/src/include/utils/guc.h Tue Jun 12 14:14:38 2001
@@ -67,5 +67,6 @@
extern bool Show_btree_build_stats;

extern bool SQL_inheritance;
+extern bool Australian_timezones;

#endif /* GUC_H */
diff -ru postgresql-7.1.2.orig/src/test/regress/expected/horology-no-DST-before-1970.out postgresql-7.1.2/src/test/regress/expected/horology-no-DST-before-1970.out
--- postgresql-7.1.2.orig/src/test/regress/expected/horology-no-DST-before-1970.out Fri Apr 6 15:50:25 2001
+++ postgresql-7.1.2/src/test/regress/expected/horology-no-DST-before-1970.out Tue Jun 12 14:14:38 2001
@@ -4,6 +4,8 @@
--
-- 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
------------------------------
diff -ru postgresql-7.1.2.orig/src/test/regress/expected/horology-solaris-1947.out postgresql-7.1.2/src/test/regress/expected/horology-solaris-1947.out
--- postgresql-7.1.2.orig/src/test/regress/expected/horology-solaris-1947.out Fri Apr 6 15:50:25 2001
+++ postgresql-7.1.2/src/test/regress/expected/horology-solaris-1947.out Tue Jun 12 14:14:38 2001
@@ -4,6 +4,8 @@
--
-- 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
------------------------------
diff -ru postgresql-7.1.2.orig/src/test/regress/expected/horology.out postgresql-7.1.2/src/test/regress/expected/horology.out
--- postgresql-7.1.2.orig/src/test/regress/expected/horology.out Fri Apr 6 15:50:25 2001
+++ postgresql-7.1.2/src/test/regress/expected/horology.out Tue Jun 12 14:14:38 2001
@@ -4,6 +4,8 @@
--
-- 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
------------------------------
diff -ru postgresql-7.1.2.orig/src/test/regress/expected/timestamp.out postgresql-7.1.2/src/test/regress/expected/timestamp.out
--- postgresql-7.1.2.orig/src/test/regress/expected/timestamp.out Fri May 4 05:00:37 2001
+++ postgresql-7.1.2/src/test/regress/expected/timestamp.out Tue Jun 12 14:14:38 2001
@@ -4,6 +4,8 @@
-- 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
------
diff -ru postgresql-7.1.2.orig/src/test/regress/sql/horology.sql postgresql-7.1.2/src/test/regress/sql/horology.sql
--- postgresql-7.1.2.orig/src/test/regress/sql/horology.sql Fri Apr 6 15:50:29 2001
+++ postgresql-7.1.2/src/test/regress/sql/horology.sql Tue Jun 12 14:14:38 2001
@@ -1,10 +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";

diff -ru postgresql-7.1.2.orig/src/test/regress/sql/timestamp.sql postgresql-7.1.2/src/test/regress/sql/timestamp.sql
--- postgresql-7.1.2.orig/src/test/regress/sql/timestamp.sql Sat Nov 25 16:00:33 2000
+++ postgresql-7.1.2/src/test/regress/sql/timestamp.sql Tue Jun 12 14:14:38 2001
@@ -1,10 +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";