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

Re: 2007 DST change not working

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: 2007 DST change not working
Date: 2007-01-01 21:17:02
Message-ID: 5.2.1.1.0.20070101160425.0551b4a0@pop6.sympatico.ca (view raw or flat)
Thread:
Lists: 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(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


In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2007-01-01 21:21:57
Subject: Re: 2007 DST change not working
Previous:From: Chad WagnerDate: 2007-01-01 20:52:00
Subject: Re: 2007 DST change not working

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