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.1 -r1.2
--- openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-create.sql	4 Sep 2001 04:41:30 -0000	1.1
+++ openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-create.sql	15 Oct 2001 19:33:38 -0000	1.2
@@ -5,10 +5,47 @@
 -- a combination of the NIH timezone database and the Unix zoneinfo
 -- database (conversion rules).
 --
--- @author jon@jongriffin.com
+-- @author jon@jongriffin.com, dhogaza@pacifier.com
 -- @creation-date 2001-09-02
 -- @cvs-id $Id$
 
+-- DRB: PostgreSQL has its own ideas about timezones and input/output conversions.
+-- It natively supports a subset of the Unix timezone database, and external
+-- representations are always in server-local time unless overridden by explicit
+-- time zone information on input or converted to varchar with an "at timezone"
+-- suffix in a select statement.
+
+-- While useful for applications that can live with the restrictions, it's not
+-- quite general enough for our usage.  This package provides the generality
+-- we need in a style that's very close to that of its Oracle equivalent.
+
+-- 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
+--   Takes an input string (which must NOT have any explicit timezone
+--   information embedded) and converts it to a timestamp, 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.
+
+-- timezone__get_date(timezone, timestamp, format string) returns varchar
+--   Converts the timestamp to a pretty date in the given timezone using "to_char"
+--   and appends the timezone abbreviation.
+
+-- timezone__get_offset(timezone, timestamp) 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
+--   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)
+
+-- Currently if timezone can't be found UTC is assumed.  Server local time
+-- might make more sense but the Oracle version assumes UTC so we'll use that
+-- for now...
+
 create sequence timezone_seq;
 
 -- Primary table for storing timezone names and standard offsets
@@ -17,27 +54,20 @@
     tz_id	   integer
 		   constraint timezones_tz_id_pk primary key,
     -- Unix-style TZ environment variable string, e.g. 'America/Los_Angeles'
-    tz		   varchar2(100) not null,
+    tz		   varchar(100) not null,
     -- the standard time offset from UTC as (+-)hhmiss
-    gmt_offset	   char(7) not null
+    gmt_offset	   text not null
 );
 
 -- add this table into the reference repository
 
-declare
-    v_id integer;
-begin
-    v_id := acs_reference.new(
-        table_name     => 'TIMEZONES',
-        package_name   => 'TIMEZONE',
-        source         => 'National Institute of Health (USA)',
-        source_url     => 'ftp://elsie.nci.nih.gov/pub',
-        last_update    => to_date('2000-08-21','YYYY-MM-DD'),
-        effective_date => sysdate
+select acs_reference__new(
+          'TIMEZONES',
+          '2000-08-21',
+          'National Institute of Health (USA)',
+          'ftp://elsie.nci.nih.gov/pub',
+          now()
     );
-commit;
-end;
-/
 
 -- The following table stores the rules for converting between
 -- local and UTC time. Each rule is specified by timezone, its
@@ -49,19 +79,17 @@
 			constraint timezone_rules_tz_id_fk references timezones
 			on delete cascade,
     -- abbreviation for local time, e.g. EST, EDT
-    abbrev		varchar2(10),
+    abbrev		varchar(10) not null,
     -- UTC start/end time of this rule
-    utc_start		date,
-    utc_end		date,
+    utc_start		timestamp not null,
+    utc_end		timestamp not null,
     -- local start/end time of this rule
-    local_start		date,
-    local_end		date,
-    -- GMT offset in fractions of day (UTC + gmt_offset = local)
-    gmt_offset		number,
+    local_start		timestamp not null,
+    local_end		timestamp not null,
+    -- GMT offset in seconds
+    gmt_offset		text not null,
     -- is Daylight Savings Time in effect for this rule?
-    isdst		char(1) 
-			constraint timezone_rules_isdist_ck
-			check (isdst in ('t','f'))
+    isdst_p		boolean
 );
 
 create index timezone_rules_idx1 on timezone_rules(tz_id, utc_start,   utc_end);
@@ -71,440 +99,241 @@
 -- TimeZone package
 -------------------------------------------------------------------------------
 
-create or replace package timezone
-as
-    procedure new (
-        tz         in timezones.tz%TYPE,
-        gmt_offset in timezones.gmt_offset%type
-    );
+create function rdbms_date(varchar) returns timestamp as '
+declare
+  p_raw_date alias for $1;
+begin
+  return timestamp (p_raw_date || ''+00'');
+end;' language 'plpgsql';
 
-    procedure delete (
-	 tz_id	   in timezones.tz_id%TYPE
-    );
+create function timezone__new (varchar, varchar) returns integer as '
+declare
+  p_tz alias for $1;
+  p_gmt_offset alias for $2;
+begin
+  insert into timezones
+    (tz_id, tz, gmt_offset)
+  values
+    (nextval(''timezone_seq''), p_tz, gmt_offset);
+  return 0;
+end;' language 'plpgsql';
+	 
+create function timezone__delete (integer) returns integer as '
+declare
+  p_tz_id alias for $1;
+begin
+  delete from timezone_rules where tz_id = p_tz_id;
+  delete from timezones      where tz_id = p_tz_id;
+  return 0;
+end;' language 'plpgsql';
 
-    function get_id (
-	 -- Gets the ID number of the given timezone
-	 tz in timezones.tz%TYPE
-    ) return integer;
+-- private function for looking up timezone id's
 
-    procedure add_rule (
-	 -- Adds a new conversion rule to the timezone_rules database
-	 tz		in timezones.tz%TYPE,
-	 abbrev		in timezone_rules.abbrev%TYPE,
-	 isdst		in integer,
-	 gmt_offset	in integer,
-	 utc_start	in varchar,
-	 utc_end	in varchar,
-	 local_start	in varchar,
-	 local_end	in varchar
-    );
+create function timezone__get_id (varchar) returns integer as '
+declare
+  p_tz alias for $1;
+  v_tz_id integer;
+begin
+  select tz_id into v_tz_id 
+  from   timezones
+  where  tz = p_tz;
+  return v_tz_id;
+end;' language 'plpgsql';
 
-    -- The following are the primary time conversion functions
+create 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;
+  p_isdst_p alias for $3;
+  p_gmt_offset alias for $4;
+  p_utc_start alias for $5;
+  p_utc_end alias for $6;
+  p_local_start alias for $7;
+  p_local_end alias for $8;
+begin
+  insert into timezone_rules
+    (tz_id, abbrev, utc_start, utc_end, local_start, local_end, gmt_offset, isdst_p)
+  select timezone__get_id(tz), abbrev, rdbms_date(utc_start),
+    rdbms_date(utc_end), rdbms_date(local_start),
+    to_date(local_end),
+    gmt_offset,
+    case isdst_p isdst_p when 0 then ''f'' else ''t''end;
+end;' language 'plpgsql';
 
-    function utc_to_local (
-	 -- Returns utc_time converted to local time
-	 tz        in timezones.tz%TYPE,
-	 utc_time  in date
-    ) return date;
+create function timezone__convert_to_utc (integer, varchar) returns timestamp as '
+declare
+  p_tz_id alias for $1;
+  p_local_varchar alias for $2;
+  v_base_time timestamp;
+foo varchar;
+begin
 
-    function utc_to_local (
-	 -- Returns utc_time converted to local time
-	 tz_id     in timezones.tz_id%TYPE,
-	 utc_time  in date
-    ) return date;
+  select timestamp (p_local_varchar || substr(gmt_offset,1,5)) into v_base_time
+  from timezones
+  where tz_id = p_tz_id;
 
-    function local_to_utc (
-	 tz_id      in timezones.tz_id%TYPE,
-	 local_time in date
-    ) return date;
+  if not found then
+    return timestamp (p_local_varchar || ''+00'');
+  end if;
 
-    function local_to_utc (
-	 tz         in timezones.tz%TYPE,
-	 local_time in date
-    ) return date;
+  return timestamp (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;
 
-    -- The following provide access to the current offset information
+end;' language 'plpgsql';
 
-    function get_offset (
-	 -- Gets the timezone offset in seconds, for the current date,
-	 -- modified in case of DST.
-	 tz_id      in timezones.tz_id%TYPE,
-	 local_time in date default sysdate
-    ) return integer;
 
-    function get_offset (
-	 tz         in timezones.tz%TYPE,
-	 local_time in date default sysdate
-    ) return integer;
+create function timezone__convert_to_utc (varchar, varchar) returns timestamp as '
+declare
+  p_tz alias for $1;
+  p_time alias for $2;
+begin
+  return timezone__local_to_utc(timezone__get_id(p_tz), p_time);
+end;' language 'plpgsql';
 
+create function timezone__get_offset (integer, timestamp) returns interval as '
+declare
+  p_tz_id alias for $1;
+  p_time alias for $2;
+  v_offset integer;
+begin
+  v_offset := ''0'';
 
-    function get_rawoffset (
-	 -- Gets the timezone offset NOT modified for DST
-	 tz_id      in timezones.tz_id%TYPE,
-	 local_time in date default sysdate
-    ) return integer;
+  select gmt_offset into v_offset
+  from timezone_rules
+  where  tz_id = p_tz_id and p_time between utc_start and utc_end;
 
-    function get_rawoffset (
-	 -- Gets the timezone offset NOT modified for DST
-	 tz         in timezones.tz%TYPE,
-	 local_time in date default sysdate
-    ) return integer;
+  return interval (v_offset || ''seconds'');
+end;' language 'plpgsql';
 
-    function get_abbrev (
-	 -- Returns abbreviation for the coversion rule
-	 tz_id	     in timezones.tz_id%TYPE,
-	 local_time  in date default sysdate
-    ) return varchar;
+create function timezone__get_offset (varchar, timestamp) returns interval as '
+declare
+  p_tz alias for $1;
+  p_time alias for $2;
+begin
+  return timezone__get_offset(timezone__get_id(p_tz), p_time);
+end;' language 'plpgsql';
+    
+create function timezone__get_rawoffset (integer, timestamp) returns interval as '
+declare
+  p_tz_id alias for $1;
+  p_time alias for $2;
+  v_offset varchar;
+begin
+  v_offset := ''0'';
 
-    function get_abbrev (
-	 -- Returns abbreviation for the coversion rule
-	 tz	     in timezones.tz%TYPE,
-	 local_time  in date default sysdate
-    ) return varchar;
+  select
+    case isdst_p
+    when ''t'' then interval (gmt_offset || ''seconds'') - ''3600 seconds''
+    else interval (gmt_offset || ''seconds'')
+    end
+  into v_offset
+  from   timezone_rules
+  where  tz_id  = p_tz_id and p_time between utc_start and utc_end;
 
-    function get_zone_offset (
-    	 -- Returns the relative offset between two zones at a
-	 -- particular UTC time. 
-	 tz_this     in timezones.tz%TYPE,
-	 tz_other    in timezones.tz%TYPE,
-	 utc_time    in date default sysdate
-    ) return integer;
+  return v_offset;
+end;' language 'plpgsql';
 
-    -- Access to flags
+create function timezone__get_rawoffset (varchar, timestamp) returns interval as '
+declare
+  p_tz alias for $1;
+  p_time for $2;
+begin
+  return timezone__get_rawoffset(timezone__get_id(p_tz), p_time);
+end;' language 'plpgsql';
 
-    function isdst_p (
-	 -- Returns 't' if timezone is currently using DST
-	 tz_id	     in timezones.tz_id%TYPE,
-	 local_time  in date default sysdate
-    ) return char;	 
+create function timezone__get_abbrev (integer, timestamp) returns varchar as '
+declare
+  p_tz_id alias for $1;
+  p_time for $2;
+  v_abbrev timezone_rules.abbrev%TYPE;
+begin
+  v_abbrev := ''GMT'';
 
-    function isdst_p (
-	 -- Returns 't' if timezone is currently using DST
-	 tz	     in timezones.tz%TYPE,
-	 local_time  in date default sysdate
-    ) return char;	 
-
-
-    -- Special formatting functions
-
-    function get_date (
-	 -- Returns a formatted date with timezone info appended  
-	 tz_id	     in timezones.tz_id%TYPE,
-	 local_time  in date,
-	 format	     in varchar default 'yyyy-mm-ss hh24:mi:ss'
-    ) return varchar;
-
-    function get_date (
-	 -- Returns a formatted date with timezone info appended  
-	 tz	     in timezones.tz%TYPE,
-	 local_time  in date,
-	 format	     in varchar default 'yyyy-mm-ss hh24:mi:ss'
-    ) return varchar;
-
-
-end timezone;
-/
-show errors
-
---
---
---
-
-create or replace package body timezone
-as
-    procedure new (
-	 tz         in timezones.tz%TYPE,
-	 gmt_offset in timezones.gmt_offset%type
-    ) 
-    is
-    begin
-	 insert into timezones
-	      (tz_id, tz, gmt_offset)
-	 values
-	      (timezone_seq.nextval, tz, gmt_offset);
-    end;
+  select abbrev into v_abbrev
+  from   timezone_rules
+  where  tz_id = p_tz_id and p_time between local_start and local_end;
 	 
-    procedure delete (
-	 tz_id	   in timezones.tz_id%TYPE
-    )
-    is
-    begin
-	 delete from timezone_rules where tz_id = tz_id;
-	 delete from timezones      where tz_id = tz_id;
-    end;
+  return v_abbrev;
+end;' language 'plpgsql';
 
-    -- private function for looking up timezone id's
+create function timezone__get_abbrev (varchar, timestamp) returns varchar as '
+declare
+  p_tz alias for $1;
+  p_time alias for $2;
+begin
+  return timezone__get_abbrev(timezone__get_id(p_tz), p_time);
+end;' language 'plpgsql';
 
-    function get_id (
-	 tz in timezones.tz%TYPE
-    ) return integer
-    is
-	 tz_id integer;
-    begin
-	 select tz_id into tz_id 
-	 from   timezones
-	 where  tz = get_id.tz;
+-- Returns a formatted date with timezone info appended  
 
-	 return tz_id;
-    end;
+create function timezone__get_date (integer, timestamp, varchar) returns varchar as '
+declare
+  p_tz_id alias for $1;
+  p_timestamp alias for $2;
+  p_format alias for $3;
+  v_timezone_offset interval;
+  v_date text;
+begin
 
-    procedure add_rule (
-	 tz		in timezones.tz%TYPE,
-	 abbrev		in timezone_rules.abbrev%TYPE,
-	 isdst		in integer,
-	 gmt_offset	in integer,
-	 utc_start	in varchar,
-	 utc_end	in varchar,
-	 local_start	in varchar,
-	 local_end	in varchar
-    )
-    is
-    begin
-	 insert into timezone_rules
-	      (tz_id,
-	       abbrev,
-	       utc_start,
-               utc_end,
-	       local_start,
-	       local_end,
-	       gmt_offset,
-	       isdst)
-	 values
-	      (get_id(tz),
-	       abbrev,
-	       to_date(utc_start,'Mon dd hh24:mi:ss yyyy'),
-	       to_date(utc_end,  'Mon dd hh24:mi:ss yyyy'),
-	       to_date(local_start,'Mon dd hh24:mi:ss yyyy'),
-	       to_date(local_end,'Mon dd hh24:mi:ss yyyy'),
-	       gmt_offset / 86400,
-	       decode(isdst,0,'f',1,'t'));
-    end;
+  select to_char(p_timestamp + interval (extract(tz from p_timestamp)|| ''seconds'') +
+         interval (gmt_offset || ''seconds''), p_format) || '' '' || 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, p_format) into v_date;
+  end if;
 
-    function utc_to_local (
-	 tz_id     in timezones.tz_id%TYPE,
-	 utc_time  in date
-    ) return date
-    is
-	 local_time date;
-    begin
-	 select utc_time + gmt_offset into local_time
-	 from   timezone_rules
-	 where  tz_id  = utc_to_local.tz_id
-	 and    utc_time between utc_start and utc_end
-	 and	rownum = 1;
+  return v_date;
 
-	 return local_time;
-    exception
-	 when no_data_found then
-	      return utc_time;
-    end utc_to_local;
+end;' language 'plpgsql';
+create function timezone__get_date (varchar, timestamp, varchar) returns varchar as '
+declare
+  p_tz alias for $1;
+  p_time alias for $2;
+  p_format alias for $3;
+begin
+  return timezone__get_date(timezone__get_id(p_tz), p_time, p_format);
+end;' language 'plpgsql';
 
-    function utc_to_local (
-	 tz        in timezones.tz%TYPE,
-	 utc_time  in date
-    ) return date
-    is
-    begin
-	 return utc_to_local(get_id(tz), utc_time);
-    end;
+-- Returns 't' if timezone is currently using DST
+create function timezone__isdst_p (integer, timestamp) returns boolean as '
+declare
+  p_tz_id alias for $1;
+  p_time alias for $2;
+  v_isdst_p boolean;
+begin
+  v_isdst_p := ''f'';
 
+  select isdst_p into v_isdst_p
+  from   timezone_rules
+  where  tz_id = p_tz_id and p_time between local_start and local_end;
 
+  return v_isdst_p;
+end;' language 'plpgsql';
 
-    function local_to_utc (
-	 tz_id      in timezones.tz_id%TYPE,
-	 local_time in date
-    ) return date
-    is
-	 utc_time date;
-    begin
-	 select local_time - gmt_offset into utc_time
-	 from   timezone_rules
-	 where  tz_id = local_to_utc.tz_id
-	 and    local_time between local_start and local_end
-	 and    rownum = 1;
+create function timezone__isdst_p (varchar, timestamp) returns boolean as '
+declare
+  p_tz alias for $1;
+  p_time alias for $2;
+begin
+  return timezone__isdst_p(timezone__get_id(p_tz), p_time);
+end;' language 'plpgsql';
 
-	 return utc_time;
-    exception
-	 when no_data_found then
-	      return local_time;
-    end;
-
-    function local_to_utc (
-	 tz         in timezones.tz%TYPE,
-	 local_time in date
-    ) return date
-    is
-    begin
-	 return local_to_utc(get_id(tz),local_time);
-    end;
-
-    function get_offset (
-	 tz_id      in timezones.tz_id%TYPE,
-	 local_time in date default sysdate
-    ) return integer
-    is
-	 v_offset integer;
-    begin
-	 select round(gmt_offset*86400,0) into v_offset
-	 from   timezone_rules
-	 where  tz_id = get_offset.tz_id
-	 and    local_time between local_start and local_end
-	 and    rownum = 1;
-
-	 return v_offset;
-    exception
-	 when no_data_found then
-	      return 0;
-    end;
-
-    function get_offset (
-	 tz         in timezones.tz%TYPE,
-	 local_time in date default sysdate
-    ) return integer
-    is
-    begin
-	 return get_offset(get_id(tz),local_time);
-    end;
+create function timezone__get_zone_offset (varchar, varchar, 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';
     
-
-    function get_rawoffset (
-	 tz_id      in timezones.tz_id%TYPE,
-	 local_time in date default sysdate
-    ) return integer
-    is
-	 v_offset number;
-    begin
-	 select decode (isdst,'t', round(gmt_offset*86400,0) - 3600, 
-                              'f', round(gmt_offset*86400,0)) into v_offset
-	 from   timezone_rules
-	 where  tz_id  = get_rawoffset.tz_id
-	 and    local_time between local_start and local_end
-	 and    rownum = 1;
-
-	 return v_offset;
-    exception
-	 when no_data_found then
-	      return 0;
-    end;
-
-    function get_rawoffset (
-	 tz         in timezones.tz%TYPE,
-	 local_time in date default sysdate
-    ) return integer
-    is
-    begin
-	 return get_rawoffset(get_id(tz),local_time);
-    end;
-
-    function get_abbrev (
-	 tz_id	     in timezones.tz_id%TYPE,
-	 local_time  in date default sysdate
-    ) return varchar
-    is
-	 v_abbrev timezone_rules.abbrev%TYPE;
-    begin
-	 select abbrev into v_abbrev
-	 from   timezone_rules
-	 where  tz_id = get_abbrev.tz_id
-	 and    local_time between local_start and local_end;
-	 
-	 return v_abbrev;
-    exception
-	 when no_data_found then
-	      return 'GMT';
-    end;
-
-    function get_abbrev (
-	 tz	     in timezones.tz%TYPE,
-	 local_time  in date default sysdate
-    ) return varchar
-    is
-    begin
-	 return get_abbrev(get_id(tz),local_time);
-    end;
-
-    function get_date (
-	 -- Returns a formatted date with timezone info appended  
-	 tz_id	     in timezones.tz_id%TYPE,
-	 local_time  in date,
-	 format	     in varchar default 'yyyy-mm-ss hh24:mi:ss'
-    ) return varchar
-    is
-	 v_date varchar(1000);
-    begin
-	 select to_char(local_time,format) || ' ' || abbrev into v_date 
-	 from   timezone_rules
-	 where  tz_id = get_date.tz_id
-	 and    local_time between local_start and local_end
-	 and    rownum = 1;
-
-	 return v_date;
-    exception
-	 when no_data_found then
-	      select to_char(local_time,format) into v_date from dual;
-	      return v_date;
-    end;
-
-    function get_date (
-	 tz	     in timezones.tz%TYPE,
-	 local_time  in date,
-	 format	     in varchar default 'yyyy-mm-ss hh24:mi:ss'
-    ) return varchar
-    is
-    begin
-	 return get_date(get_id(tz),local_time,format);
-    end;
-
-
-    function isdst_p (
-	 -- Returns 't' if timezone is currently using DST
-	 tz_id	     in timezones.tz_id%TYPE,
-	 local_time  in date default sysdate
-    ) return char
-    is
-	 v_isdst char;
-    begin
-	 select isdst into v_isdst
-	 from   timezone_rules
-	 where  tz_id = isdst_p.tz_id
-	 and    local_time between local_start and local_end
-	 and    rownum = 1;
-
-	 return v_isdst;
-    exception
-	 when no_data_found then
-	      return 'f';
-    end;
-
-    function isdst_p (
-	 tz	     in timezones.tz%TYPE,
-	 local_time  in date default sysdate
-    ) return char
-    is
-    begin
-	 return isdst_p (get_id(tz),local_time);
-    end;
-
-    function get_zone_offset (
-	 tz_this     in timezones.tz%TYPE,
-	 tz_other    in timezones.tz%TYPE,
-	 utc_time    in date default sysdate
-    ) return integer
-    is
-    begin
-	 return get_offset(tz_this, utc_to_local(tz_this, utc_time)) -
-	        get_offset(tz_other,utc_to_local(tz_other,utc_time));
-    end;
-    
-end timezone; 
-/
-show errors
-
 -------------------------------------------------------------------------------
 -- TimeZone data
 -------------------------------------------------------------------------------
-/i ref-timezones-data.sql
-/i ../common/ref-timezone-rules.sql
-
-
+\i ../common/ref-timezones-data.sql
+\i ../common/ref-timezones-rules.sql