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;