Postgres Pro
Downloads
Home   >   mailing lists

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

From Chad Wagner
Subject Re: 2007 DST change not working
Date
Msg-id 81961ff50701011336y38d9b29ch6e9e3eb9b482c81a@mail.gmail.com
Whole thread Raw
In response to Re: 2007 DST change not working  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: 2007 DST change not working
List pgsql-novice
Tree view
Tom,
    I compared Canada/Eastern to US/Eastern and they are out of date for 8.1.5, but 8.2 is OK.

PostgreSQL 8.1.5
$ /usr/sbin/zdump -v /usr/share/pgsql/timezone/Canada/Eastern  |grep 2007
/usr/share/pgsql/timezone/Canada/Eastern  Sun Apr  1 06:59:59 2007 UTC = Sun Apr  1 01:59:59 2007 EST isdst=0 gmtoff=-18000
/usr/share/pgsql/timezone/Canada/Eastern  Sun Apr  1 07:00:00 2007 UTC = Sun Apr  1 03:00:00 2007 EDT isdst=1 gmtoff=-14400
/usr/share/pgsql/timezone/Canada/Eastern  Sun Oct 28 05:59:59 2007 UTC = Sun Oct 28 01:59:59 2007 EDT isdst=1 gmtoff=-14400
/usr/share/pgsql/timezone/Canada/Eastern  Sun Oct 28 06:00:00 2007 UTC = Sun Oct 28 01:00:00 2007 EST isdst=0 gmtoff=-18000

PostgreSQL 8.2.0
$ /usr/sbin/zdump -v /usr/share/pgsql/timezone/Canada/Eastern  |grep 2007
/usr/share/pgsql/timezone/Canada/Eastern  Sun Mar 11 06:59:59 2007 UTC = Sun Mar 11 01:59:59 2007 EST isdst=0 gmtoff=-18000
/usr/share/pgsql/timezone/Canada/Eastern  Sun Mar 11 07:00:00 2007 UTC = Sun Mar 11 03:00:00 2007 EDT isdst=1 gmtoff=-14400
/usr/share/pgsql/timezone/Canada/Eastern  Sun Nov  4 05:59:59 2007 UTC = Sun Nov  4 01:59:59 2007 EDT isdst=1 gmtoff=-14400
/usr/share/pgsql/timezone/Canada/Eastern  Sun Nov  4 06:00:00 2007 UTC = Sun Nov  4 01:00:00 2007 EST isdst=0 gmtoff=-18000


On 1/1/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Frank Bax <fbax@sympatico.ca> writes:
> 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.

You certain this is PG 8.1?  Every release of 8.1.x has shipped with
timezone data files that included the 2007 rule change.  In the 8.0
series, 8.0.4 and up should have that update.  PG 7.x releases are
dependent on the operating system's timezone knowledge.

FWIW, my copy of 8.1.5 produces

regression=# 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)

where

regression=# show timezone;
     TimeZone
------------------
America/New_York
(1 row)

(Another possibility I guess is you have the wrong timezone setting?)

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: 2007 DST change not working
Next
From: "Chad Wagner"
Date:
Subject: Re: 2007 DST change not working