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 -r1.6
--- openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-create.sql	23 Feb 2002 20:15:55 -0000	1.5
+++ openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-create.sql	17 May 2003 11:11:11 -0000	1.6
@@ -22,22 +22,23 @@
 -- 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 the same date/time shifted to UTC.
+--   is a date/time local to the given timezone while the returned timestamptz
+--   is the same date/time shifted to UTC *if* you ignore the timezone information
+--   by for instance extracting the information with to_char().
 
--- 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 +47,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 +91,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,14 +109,14 @@
 -- TimeZone package
 -------------------------------------------------------------------------------
 
-create function rdbms_date(varchar) returns timestamp as '
+create or replace 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 '
+create or replace function timezone__new (varchar, varchar) returns integer as '
 declare
   p_tz alias for $1;
   p_gmt_offset alias for $2;
@@ -118,7 +128,7 @@
   return 0;
 end;' language 'plpgsql';
 	 
-create function timezone__delete (integer) returns integer as '
+create or replace function timezone__delete (integer) returns integer as '
 declare
   p_tz_id alias for $1;
 begin
@@ -129,7 +139,7 @@
 
 -- private function for looking up timezone id's
 
-create function timezone__get_id (varchar) returns integer as '
+create or replace function timezone__get_id (varchar) returns integer as '
 declare
   p_tz alias for $1;
   v_tz_id integer;
@@ -140,7 +150,7 @@
   return v_tz_id;
 end;' language 'plpgsql';
 
-create function timezone__add_rule (varchar, varchar, integer, varchar, varchar, varchar, varchar, varchar) returns integer as '
+create or replace function timezone__add_rule (varchar, varchar, integer, varchar, varchar, varchar, varchar, varchar) returns integer as '
 declare
   p_tz alias for $1;
   p_abbrev alias for $2;
@@ -160,29 +170,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 or replace 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;
-foo varchar;
+  v_base_time timestamptz;
 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) - "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 or replace function timezone__get_offset (integer, timestamptz) returns interval as '
 declare
   p_tz_id alias for $1;
   p_time alias for $2;
@@ -194,10 +204,10 @@
   from timezone_rules
   where  tz_id = p_tz_id and p_time between utc_start and utc_end;
 
-  return interval (v_offset || ''seconds'');
+  return "interval" (v_offset || ''seconds'');
 end;' language 'plpgsql';
     
-create function timezone__get_rawoffset (integer, timestamp) returns interval as '
+create or replace function timezone__get_rawoffset (integer, timestamptz) returns interval as '
 declare
   p_tz_id alias for $1;
   p_time alias for $2;
@@ -207,8 +217,8 @@
 
   select
     case isdst_p
-    when ''t'' then interval (gmt_offset || ''seconds'') - ''3600 seconds''
-    else interval (gmt_offset || ''seconds'')
+    when ''t'' then "interval" (gmt_offset || ''seconds'') - ''3600 seconds''
+    else "interval" (gmt_offset || ''seconds'')
     end
   into v_offset
   from   timezone_rules
@@ -217,7 +227,7 @@
   return v_offset;
 end;' language 'plpgsql';
 
-create function timezone__get_abbrev (integer, timestamp) returns varchar as '
+create or replace function timezone__get_abbrev (integer, timestamptz) returns varchar as '
 declare
   p_tz_id alias for $1;
   p_time for $2;
@@ -234,13 +244,13 @@
 
 -- Returns a formatted date with timezone info appended  
 
-create function timezone__get_date (integer, timestamp, varchar, boolean) returns varchar as '
+create or replace 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
@@ -252,14 +262,15 @@
     where  tz_id = p_tz_id and p_timestamp between utc_start and utc_end;
   end if;
 
-  select to_char(p_timestamp + interval (extract(tz from p_timestamp) || ''seconds'') +
-         interval (gmt_offset || ''seconds''), p_format) || '' '' || v_abbrev
+  select to_char(p_timestamp + "interval" (
+     (extract(timezone_hour from p_timestamp) * 3600 + extract(timezone_minute from p_timestamp) * 60) || ''seconds'') +
+         "interval" (gmt_offset || ''seconds''), p_format) || '' '' || v_abbrev
     into v_date 
   from   timezone_rules
   where  tz_id = p_tz_id and p_timestamp between utc_start and utc_end;
 
   if not found then
-    select to_char(p_timestamp + interval (extract (tz from p_timestamp) || ''seconds''), p_format)
+    select to_char(p_timestamp + "interval" ((extract(timezone_hour from p_timestamp) * 3600 + extract(timezone_minute from p_timestamp) * 60) || ''seconds''), p_format)
       into v_date;
   end if;
 
@@ -268,7 +279,7 @@
 end;' language 'plpgsql';
 
 -- Returns 't' if timezone is currently using DST
-create function timezone__isdst_p (integer, timestamp) returns boolean as '
+create or replace function timezone__isdst_p (integer, timestamptz) returns boolean as '
 declare
   p_tz_id alias for $1;
   p_time alias for $2;
@@ -282,16 +293,6 @@
 
   return v_isdst_p;
 end;' language 'plpgsql';
-
-create function timezone__get_zone_offset (integer, integer, timestamp) returns interval as '
-declare
-  p_tz_this alias for $1;
-  p_tz_other alias for $2;
-  p_time alias for $3;
-begin
-  return timezone__get_offset(p_tz_this, timezone__utc_to_local(p_tz_this, p_time)) -
-	 timezone__get_offset(p_tz_other, timezone__utc_to_local(p_tz_other, p_time));
-end;' language 'plpgsql';
     
 -------------------------------------------------------------------------------
 -- TimeZone data