Re: 2007 DST change not working

OK, I got the dates wrong in my msg; it should be Mar11 and Nov4 (Canada
follows USA); but pgsql still isn't reporting the correct dates on my system:

fbax=> select version();
version

-----------------------------------------------------------------------------------------
PostgreSQL 8.1.5 on i386-unknown-openbsd4.0, compiled by GCC cc (GCC)
3.3.5 (propolice)
(1 row)

fbax=> show time zone;
TimeZone
----------------
Canada/Eastern

$ /usr/sbin/zdump -v /usr/share/zoneinfo/Canada/Eastern | grep 2007
/usr/share/zoneinfo/Canada/Eastern Sun Mar 11 06:59:59 2007 UTC = Sun Mar
11 01:59:59 2007 EST isdst=0
/usr/share/zoneinfo/Canada/Eastern Sun Mar 11 07:00:00 2007 UTC = Sun Mar
11 03:00:00 2007 EDT isdst=1
/usr/share/zoneinfo/Canada/Eastern Sun Nov 4 05:59:59 2007 UTC = Sun
Nov 4 01:59:59 2007 EDT isdst=1
/usr/share/zoneinfo/Canada/Eastern Sun Nov 4 06:00:00 2007 UTC = Sun
Nov 4 01:00:00 2007 EST isdst=0

$ ls -ltr /etc/localtime
lrwxr-xr-x 1 root wheel 34 Dec 28 16:51 /etc/localtime ->
/usr/share/zoneinfo/Canada/Eastern

At 03:52 PM 1/1/07, Chad Wagner wrote:

>Perhaps your installation of PostgreSQL has outdated zone files, or a
>wrong time zone set? Also, for the US DST begins on March 11 and ends on
>November 4.
>
>Check the commands below to see what your results are.
>
>wagnerch=# select version();
> version
>
>-------------------------------------------------------------------------------------------------------
>
> PostgreSQL 8.1.5 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
> 3.4.6 20060404 (Red Hat 3.4.6-3)
>(1 row)
>
>wagnerch=# show time zone;
> TimeZone
>----------
> EST5EDT
>(1 row)
>
>wagnerch=# drop table dst;
>DROP TABLE
>wagnerch=# create table dst (tz1 timestamp with time zone, tz2 timestamp
>with time
>wagnerch(# zone, tzage interval);
>CREATE TABLE
>wagnerch=# insert into dst ( select
>('2005-01-02'::date+(days*7))::timestamptz as t1
>wagnerch(# from generate_series(0,150) as days );
>INSERT 0 151
>wagnerch=# update dst set tz2 = tz1::timestamp + '12 hours';
>UPDATE 151
>wagnerch=# update dst set tzage = age( tz2 at time zone 'GMT', tz1 at time
>zone 'GMT' );
>UPDATE 151
>wagnerch=# select * from dst where tzage <> '12 hrs';
> tz1 | tz2 | tzage
>------------------------+------------------------+----------
> 2005-04-03 00:00:00-05 | 2005-04-03 12:00:00-04 | 11:00:00
> 2005-10-30 00:00:00-04 | 2005-10-30 12:00:00-05 | 13:00:00
> 2006-04-02 00:00:00-05 | 2006-04-02 12:00:00-04 | 11:00:00
> 2006-10-29 00:00:00-04 | 2006-10-29 12:00:00-05 | 13:00:00
> 2007-03-11 00:00:00-05 | 2007-03-11 12:00:00-04 | 11:00:00
> 2007-11-04 00:00:00-04 | 2007-11-04 12:00:00-05 | 13:00:00
>(6 rows)
>
>[wagnerch(at)host-0-243 ~]$ /usr/sbin/zdump -v
>/usr/share/pgsql/timezone/EST5EDT |grep 2007
>/usr/share/pgsql/timezone/EST5EDT Sun Mar 11 06:59:59 2007 UTC = Sun Mar
>11 01:59:59 2007 EST isdst=0 gmtoff=-18000
>/usr/share/pgsql/timezone/EST5EDT Sun Mar 11 07:00:00 2007 UTC = Sun Mar
>11 03:00:00 2007 EDT isdst=1 gmtoff=-14400
>/usr/share/pgsql/timezone/EST5EDT Sun Nov 4 05:59:59 2007 UTC = Sun
>Nov 4 01:59:59 2007 EDT isdst=1 gmtoff=-14400
>/usr/share/pgsql/timezone/EST5EDT Sun Nov 4 06:00:00 2007 UTC = Sun
>Nov 4 01:00:00 2007 EST isdst=0 gmtoff=-18000
>
>
>On 1/1/07, Frank Bax <<mailto:fbax(at)sympatico(dot)ca>fbax(at)sympatico(dot)ca> wrote:
>>Another DST related problem in pgsql 8.1.5 ...
>>
>>drop table dst;
>>create table dst (tz1 timestamp with time zone, tz2 timestamp with time
>>zone, tzage interval);
>>insert into dst ( select ('2005-01-02'::date+(days*7))::timestamptz as t1
>>from generate_series(0,150) as days );
>>update dst set tz2 = tz1::timestamp + '12 hours';
>>update dst set tzage = age( tz2 at time zone 'GMT', tz1 at time zone 'GMT' );
>>select * from dst where tzage <> '12 hrs';
>> tz1 | tz2 | tzage
>>------------------------+------------------------+----------
>> 2005-04-03 00:00:00-05 | 2005-04-03 12:00:00-04 | 11:00:00
>> 2005-10-30 00:00:00-04 | 2005-10-30 12:00:00-05 | 13:00:00
>> 2006-04-02 00:00:00-05 | 2006-04-02 12:00:00-04 | 11:00:00
>> 2006-10-29 00:00:00-04 | 2006-10-29 12:00:00-05 | 13:00:00
>> 2007-04-01 00:00:00-05 | 2007-04-01 12:00:00-04 | 11:00:00
>> 2007-10-28 00:00:00-04 | 2007-10-28 12:00:00-05 | 13:00:00
>>(6 rows)
>>
>>My query produces the correct results for 2005 and 2006; but in 2007 DST
>>dates are changing in North America and my query seems to still be using
>>2006 rules for 2007 data. What can I do about this? Query should report
>>2007-03-18 and 2007-11-11.
>>
>>Frank