Re: 2007 DST change not working

The zone files for Canada/Eastern on version 8.1.5 are out of date. A
simple work around would be to use US/Eastern time zone.

On 1/1/07, Frank Bax <fbax(at)sympatico(dot)ca> wrote:
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>