Index: openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-create.sql,v
diff -u -r1.5.4.1 -r1.5.4.2
--- openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-create.sql	18 Feb 2003 10:02:55 -0000	1.5.4.1
+++ openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-create.sql	2 Mar 2003 22:51:16 -0000	1.5.4.2
@@ -22,22 +22,22 @@
 -- PG stores all dates shifted to UTC and does all computations in Julian
 -- dates.  This package provides some very simple utilities:
 
--- timezone__convert_to_utc(timezone, input_string) returns timestamp
+-- timezone__convert_to_utc(timezone, input_string) returns timestamptz
 --   Takes an input string (which must NOT have any explicit timezone
---   information embedded) and converts it to a timestamp, shifting it
+--   information embedded) and converts it to a timestamptz, shifting it
 --   to UTC using the timezone information.   In other words, input_string
---   is a date/time local to the given timezone while the returned timestamp
+--   is a date/time local to the given timezone while the returned timestamptz
 --   is the same date/time shifted to UTC.
 
--- timezone__get_date(timezone, timestamp, format string) returns varchar
---   Converts the timestamp to a pretty date in the given timezone using "to_char"
+-- timezone__get_date(timezone, timestamptz, format string) returns varchar
+--   Converts the timestamptz to a pretty date in the given timezone using "to_char"
 --   and appends the timezone abbreviation.
 
--- timezone__get_offset(timezone, timestamp) returns interval
+-- timezone__get_offset(timezone, timestamptz) returns interval
 --   Returns a PostgreSQL interval (which can be added or substracted from
 --   a UTC timestamp) for the timestamp in the given timezone.
 
--- timezone__get_rawoffset(timezone, timestamp) returns interval
+-- timezone__get_rawoffset(timezone, timestamptz) returns interval
 --   Returns the raw (i.e. not adjusted for daylight savings time) offset
 --   for the timestamp in the timezone (those reading the code for the first
 --   time may think these definitions are backwards, but they're not)
@@ -46,6 +46,15 @@
 -- might make more sense but the Oracle version assumes UTC so we'll use that
 -- for now...
 
+-- DRB: Additional note ...
+
+-- As of version 7.3, PostgreSQL's default timestamp type no longer includes timezone
+-- information.  If we were starting from scratch, these functions could be simplified
+-- but ... we have existing OpenACS 4.x installations running PG 7.2.  pg_dump dumps
+-- the old timestamp type as timestamp with time zone explicitly, and the values include
+-- timezone information, so we're pretty much stuck using timestamptz indefinitely it
+-- appears.
+
 create sequence timezone_seq;
 
 -- Primary table for storing timezone names and standard offsets
@@ -81,11 +90,11 @@
     -- abbreviation for local time, e.g. EST, EDT
     abbrev		varchar(10) not null,
     -- UTC start/end time of this rule
-    utc_start		timestamp not null,
-    utc_end		timestamp not null,
+    utc_start		timestamptz not null,
+    utc_end		timestamptz not null,
     -- local start/end time of this rule
-    local_start		timestamp not null,
-    local_end		timestamp not null,
+    local_start		timestamptz not null,
+    local_end		timestamptz not null,
     -- GMT offset in seconds
     gmt_offset		text not null,
     -- is Daylight Savings Time in effect for this rule?
@@ -99,11 +108,11 @@
 -- TimeZone package
 -------------------------------------------------------------------------------
 
-create function rdbms_date(varchar) returns timestamp as '
+create function rdbms_date(varchar) returns timestamptz as '
 declare
   p_raw_date alias for $1;
 begin
-  return "timestamp" (p_raw_date || ''+00'');
+  return "timestamptz" (p_raw_date || ''+00'');
 end;' language 'plpgsql';
 
 create function timezone__new (varchar, varchar) returns integer as '
@@ -160,29 +169,29 @@
     case isdst_p isdst_p when 0 then ''f'' else ''t''end;
 end;' language 'plpgsql';
 
-create function timezone__convert_to_utc (integer, varchar) returns timestamp as '
+create function timezone__convert_to_utc (integer, varchar) returns timestamptz as '
 declare
   p_tz_id alias for $1;
   p_local_varchar alias for $2;
-  v_base_time timestamp;
+  v_base_time timestamptz;
 foo varchar;
 begin
 
-  select "timestamp" (p_local_varchar || substr(gmt_offset,1,5)) into v_base_time
+  select "timestamptz" (p_local_varchar || substr(gmt_offset,1,5)) into v_base_time
   from timezones
   where tz_id = p_tz_id;
 
   if not found then
-    return "timestamp" (p_local_varchar || ''+00'');
+    return "timestamptz" (p_local_varchar || ''+00'');
   end if;
 
-  return "timestamp" (p_local_varchar || ''+00'') - "interval" (gmt_offset || ''seconds'')
+  return "timestamptz" (p_local_varchar || ''+00'') - "interval" (gmt_offset || ''seconds'')
   from   timezone_rules
   where  tz_id = p_tz_id and v_base_time between utc_start and utc_end;
 
 end;' language 'plpgsql';
 
-create function timezone__get_offset (integer, timestamp) returns interval as '
+create function timezone__get_offset (integer, timestamptz) returns interval as '
 declare
   p_tz_id alias for $1;
   p_time alias for $2;
@@ -197,7 +206,7 @@
   return "interval" (v_offset || ''seconds'');
 end;' language 'plpgsql';
     
-create function timezone__get_rawoffset (integer, timestamp) returns interval as '
+create function timezone__get_rawoffset (integer, timestamptz) returns interval as '
 declare
   p_tz_id alias for $1;
   p_time alias for $2;
@@ -217,7 +226,7 @@
   return v_offset;
 end;' language 'plpgsql';
 
-create function timezone__get_abbrev (integer, timestamp) returns varchar as '
+create function timezone__get_abbrev (integer, timestamptz) returns varchar as '
 declare
   p_tz_id alias for $1;
   p_time for $2;
@@ -234,13 +243,13 @@
 
 -- Returns a formatted date with timezone info appended  
 
-create function timezone__get_date (integer, timestamp, varchar, boolean) returns varchar as '
+create function timezone__get_date (integer, timestamptz, varchar, boolean) returns varchar as '
 declare
   p_tz_id alias for $1;
   p_timestamp alias for $2;
   p_format alias for $3;
   p_append_timezone_p alias for $4;
-  v_timestamp timestamp;
+  v_timestamp timestamptz;
   v_abbrev text;
   v_date text;
 begin
@@ -269,7 +278,7 @@
 end;' language 'plpgsql';
 
 -- Returns 't' if timezone is currently using DST
-create function timezone__isdst_p (integer, timestamp) returns boolean as '
+create function timezone__isdst_p (integer, timestamptz) returns boolean as '
 declare
   p_tz_id alias for $1;
   p_time alias for $2;
@@ -284,7 +293,7 @@
   return v_isdst_p;
 end;' language 'plpgsql';
 
-create function timezone__get_zone_offset (integer, integer, timestamp) returns interval as '
+create function timezone__get_zone_offset (integer, integer, timestamptz) returns interval as '
 declare
   p_tz_this alias for $1;
   p_tz_other alias for $2;
