Re: 2007 DST change not working - Mailing list pgsql-novice

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@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@sympatico.ca>fbax@sympatico.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