Skip site navigation (1) Skip section navigation (2)

Re: 2007 DST change not working

From: "Chad Wagner" <chad(dot)wagner(at)gmail(dot)com>
To: "Frank Bax" <fbax(at)sympatico(dot)ca>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: 2007 DST change not working
Date: 2007-01-01 21:39:25
Message-ID: 81961ff50701011339k16aa8560veee15ea8e857d573@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2007-01-01 21:41:36
Subject: Re: 2007 DST change not working
Previous:From: Chad WagnerDate: 2007-01-01 21:38:03
Subject: Re: 2007 DST change not working

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group