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 20:52:00
Message-ID: 81961ff50701011252t35f14918jadef0645aab4c7a5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.620060404 (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 <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 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>        message can get through to the mailing list cleanly
>

In response to

pgsql-novice by date

Next:From: Frank BaxDate: 2007-01-01 21:17:02
Subject: Re: 2007 DST change not working
Previous:From: Frank BaxDate: 2007-01-01 19:56:06
Subject: 2007 DST change not working

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