Re: 2007 DST change not working

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

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
>

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

Frank Bax <fbax(at)sympatico(dot)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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Frank Bax <fbax(at)sympatico(dot)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
>

Correction, I checked Canada/Eastern on both. US/Eastern is fine on both.

On 1/1/07, Chad Wagner <chad(dot)wagner(at)gmail(dot)com> wrote:
>
> 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >
> > Frank Bax <fbax(at)sympatico(dot)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
> >
>
>

The zone files for Canada/Eastern on version 8.1.5 are out of date. A
simple work around would be to use US/Eastern time zone.

On 1/1/07, Frank Bax <fbax(at)sympatico(dot)ca> wrote:
>
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

Frank Bax <fbax(at)sympatico(dot)ca> writes:
> fbax=> show time zone;
> TimeZone
> ----------------
> Canada/Eastern

Oh, there's your problem: as of 8.1.5's version of the zoneinfo files,
it wasn't clear what Canada was going to do. Looking at our CVS history
it seems the zoneinfo people only did something about it in mid-2006:

+# From Paul Eggert (2006-06-27):
+# For now, assume all of DST-observing Canada will fall into line with the
+# new US DST rules,

This is in CVS for 8.1.6 but not released yet. What I'd suggest is
grabbing the share/timezone/* files out of an 8.2.0 release, which
does have the update. You might be able to get away with just copying
your /usr/share/zoneinfo tree into Postgres' share/timezone directory,
but be prepared to undo that because it could break things entirely:
there's more than one zoneinfo file format out there.

Also note you need to restart the postmaster after updating zoneinfo,
I don't think there's any provision for noticing updates automatically.

regards, tom lane

Frank,
Of course you could always get brave and recompile the zone files :). I
outlined a procedure on my web site:

http://www.postgresqlforums.com/wiki/

If your interested in braving those waters. I did perform it against
8.1.5and the zone files worked fine with your test case.

Perhaps this is a good excuse to give 8.2 a whirl? :)

On 1/1/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Frank Bax <fbax(at)sympatico(dot)ca> writes:
> > fbax=> show time zone;
> > TimeZone
> > ----------------
> > Canada/Eastern
>
> Oh, there's your problem: as of 8.1.5's version of the zoneinfo files,
> it wasn't clear what Canada was going to do. Looking at our CVS history
> it seems the zoneinfo people only did something about it in mid-2006:
>
> +# From Paul Eggert (2006-06-27):
> +# For now, assume all of DST-observing Canada will fall into line with
> the
> +# new US DST rules,
>
> This is in CVS for 8.1.6 but not released yet. What I'd suggest is
> grabbing the share/timezone/* files out of an 8.2.0 release, which
> does have the update. You might be able to get away with just copying
> your /usr/share/zoneinfo tree into Postgres' share/timezone directory,
> but be prepared to undo that because it could break things entirely:
> there's more than one zoneinfo file format out there.
>
> Also note you need to restart the postmaster after updating zoneinfo,
> I don't think there's any provision for noticing updates automatically.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

I still haven't found the zone files on my system. The file I dumped in a
previous message was from OS ( At the time, I thought it was the same thing).

I think I'll do the "US/Eastern" work around for now. I notice that I must
reload tables for this work correctly (or did I miss something)? I'm still
testing upgrade from 7.3.5 to 8.1.5, so reoad is not a problem this time.

I used to install from source about 5 years ago; but now I've been spoiled
by binary packages from OpenBSD. They have 8.1.5, but not yet 8.2
available yet.

At 07:00 PM 1/1/07, Chad Wagner wrote:

>Frank,
>Of course you could always get brave and recompile the zone files :). I
>outlined a procedure on my web site:
>
><http://www.postgresqlforums.com/wiki/>http://www.postgresqlforums.com/wiki/
>
>If your interested in braving those waters. I did perform it against
>8.1.5 and the zone files worked fine with your test case.
>
>Perhaps this is a good excuse to give 8.2 a whirl? :)
>
>On 1/1/07, Tom Lane <<mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>Frank Bax <<mailto:fbax(at)sympatico(dot)ca>fbax(at)sympatico(dot)ca> writes:
>> > fbax=> show time zone;
>> > TimeZone
>> > ----------------
>> > Canada/Eastern
>>
>>Oh, there's your problem: as of 8.1.5's version of the zoneinfo files,
>>it wasn't clear what Canada was going to do. Looking at our CVS history
>>it seems the zoneinfo people only did something about it in mid-2006:
>>
>>+# From Paul Eggert (2006-06-27):
>>+# For now, assume all of DST-observing Canada will fall into line with the
>>+# new US DST rules,
>>
>>This is in CVS for 8.1.6 but not released yet. What I'd suggest is
>>grabbing the share/timezone/* files out of an 8.2.0 release, which
>>does have the update. You might be able to get away with just copying
>>your /usr/share/zoneinfo tree into Postgres' share/timezone directory,
>>but be prepared to undo that because it could break things entirely:
>>there's more than one zoneinfo file format out there.
>>
>>Also note you need to restart the postmaster after updating zoneinfo,
>>I don't think there's any provision for noticing updates automatically.
>>
>> regards, tom lane
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match

On 1/4/07, Frank Bax <fbax(at)sympatico(dot)ca> wrote:
>
> I still haven't found the zone files on my system. The file I dumped in a
> previous message was from OS ( At the time, I thought it was the same
> thing).

I would imagine if you are using OpenBSD then they are probably in
/usr/local/share/pgsql/timezone, that seems to be the norm for non-core BSD
code.

I think I'll do the "US/Eastern" work around for now. I notice that I must
> reload tables for this work correctly (or did I miss something)? I'm
> still
> testing upgrade from 7.3.5 to 8.1.5, so reoad is not a problem this time.

I would imagine if your data is already stored with the "old" time zone,
then it would need to be reloaded. If the backend converts everything to
GMT then it would have done the conversion incorrectly due to the outdate
time zone maps, or if the backend stores the time zone with the row then
since the time zone maps have not been updated then it would still be
incorrect.

I used to install from source about 5 years ago; but now I've been spoiled
> by binary packages from OpenBSD. They have 8.1.5, but not yet 8.2
> available yet.
>

Yep, rpm has spoiled me for years on Linux. I remember the mid 90's and
building Linux 1.2.13 and 2.0 kernels, enabling and disabling features,
rebooting, total nightmare. Best two software solutions are loadable
modules and packages. :)