Index: openacs-4/contrib/obsolete-packages/photo-album-lite/photo-album-lite.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/photo-album-lite/photo-album-lite.info,v
diff -u -r1.2 -r1.3
--- openacs-4/contrib/obsolete-packages/photo-album-lite/photo-album-lite.info 16 Jan 2003 13:54:40 -0000 1.2
+++ openacs-4/contrib/obsolete-packages/photo-album-lite/photo-album-lite.info 17 May 2003 11:02:30 -0000 1.3
@@ -16,7 +16,7 @@
ArsDigita Corporation
Photo Album Lite is a simple application for storing photo files. Users can upload multiple photos and ZIP archives at a time and organize them into folders. Proper configuration of ImageMagick and Intermedia are assumed. For more information, please install the package and point your browser to /doc/photo-album-lite.
-
+
Index: openacs-4/packages/press/press.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/press/press.info,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/press/press.info 23 Jan 2003 05:50:35 -0000 1.4
+++ openacs-4/packages/press/press.info 17 May 2003 11:04:29 -0000 1.5
@@ -17,7 +17,7 @@
-
+
Index: openacs-4/packages/ref-countries/ref-countries.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-countries/ref-countries.info,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/ref-countries/ref-countries.info 2 Sep 2001 17:06:29 -0000 1.3
+++ openacs-4/packages/ref-countries/ref-countries.info 17 May 2003 11:05:58 -0000 1.4
@@ -18,7 +18,7 @@
Countries supplies the ISO 3166 Country data.
-
+
Index: openacs-4/packages/ref-currency/ref-currency.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-currency/ref-currency.info,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/ref-currency/ref-currency.info 23 Sep 2002 23:32:27 -0000 1.4
+++ openacs-4/packages/ref-currency/ref-currency.info 17 May 2003 11:07:06 -0000 1.5
@@ -19,7 +19,7 @@
It provides ISO 4217 compatibility.
-
+
Index: openacs-4/packages/ref-language/ref-language.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-language/ref-language.info,v
diff -u -r1.5 -r1.6
--- openacs-4/packages/ref-language/ref-language.info 15 Oct 2001 19:33:35 -0000 1.5
+++ openacs-4/packages/ref-language/ref-language.info 17 May 2003 11:07:51 -0000 1.6
@@ -19,7 +19,7 @@
You should use this if you are using the reference data package as many other reference data packages are dependent on this.
-
+
Index: openacs-4/packages/ref-timezones/ref-timezones.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-timezones/ref-timezones.info,v
diff -u -r1.4 -r1.5
--- openacs-4/packages/ref-timezones/ref-timezones.info 16 Jan 2003 13:55:35 -0000 1.4
+++ openacs-4/packages/ref-timezones/ref-timezones.info 17 May 2003 11:09:18 -0000 1.5
@@ -20,18 +20,9 @@
-
+
-
-
-
-
-
-
-
-
-
Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/ref-timezones/sql/common/ref-timezones-data.csv'.
Fisheye: No comparison available. Pass `N' to diff?
Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/ref-timezones/sql/common/ref-timezones-rules.csv'.
Fisheye: No comparison available. Pass `N' to diff?
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
Index: openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-drop.sql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-drop.sql 26 Oct 2001 18:43:10 -0000 1.3
+++ openacs-4/packages/ref-timezones/sql/postgresql/ref-timezones-drop.sql 17 May 2003 11:11:11 -0000 1.4
@@ -29,9 +29,9 @@
drop function timezone__get_id (varchar);
drop function timezone__add_rule (varchar, varchar, integer, varchar, varchar, varchar, varchar, varchar);
drop function timezone__convert_to_utc (integer, varchar);
-drop function timezone__get_offset (integer, timestamp);
-drop function timezone__get_rawoffset (integer, timestamp);
-drop function timezone__get_abbrev (integer, timestamp);
-drop function timezone__get_date (integer, timestamp, varchar, boolean);
-drop function timezone__isdst_p (integer, timestamp);
-drop function timezone__get_zone_offset (integer, integer, timestamp);
+drop function timezone__get_offset (integer, timestamptz);
+drop function timezone__get_rawoffset (integer, timestamptz);
+drop function timezone__get_abbrev (integer, timestamptz);
+drop function timezone__get_date (integer, timestamptz, varchar, boolean);
+drop function timezone__isdst_p (integer, timestamptz);
+drop function timezone__get_zone_offset (integer, integer, timestamptz);