Re: 2007 DST change not working

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
>