Re: 2007 DST change not working - Mailing list pgsql-novice
| From | Chad Wagner |
|---|---|
| Subject | Re: 2007 DST change not working |
| Date | |
| Msg-id | 81961ff50701011252t35f14918jadef0645aab4c7a5@mail.gmail.com Whole thread Raw |
| In response to | 2007 DST change not working (Frank Bax <fbax@sympatico.ca>) |
| List | 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.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@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
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@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@sympatico.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@postgresql.org so that your
message can get through to the mailing list cleanly
pgsql-novice by date: