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

Search Discussions

  • Chad Wagner at Jan 1, 2007 at 8:52 pm
    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@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 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
  • Frank Bax at Jan 1, 2007 at 9:19 pm
    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@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 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
  • Chad Wagner at Jan 1, 2007 at 9:39 pm
    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 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@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 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
  • Tom Lane at Jan 1, 2007 at 9:41 pm

    Frank Bax 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
  • Chad Wagner at Jan 2, 2007 at 12:00 am
    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 wrote:

    Frank Bax <fbax@sympatico.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
  • Frank Bax at Jan 4, 2007 at 2:32 pm
    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 wrote:
    Frank Bax < fbax@sympatico.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
  • Chad Wagner at Jan 4, 2007 at 3:02 pm

    On 1/4/07, Frank Bax 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. :)
  • Tom Lane at Jan 1, 2007 at 9:22 pm

    Frank Bax 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
  • Chad Wagner at Jan 1, 2007 at 9:36 pm
    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 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
  • Chad Wagner at Jan 1, 2007 at 9:38 pm
    Correction, I checked Canada/Eastern on both. US/Eastern is fine on both.
    On 1/1/07, Chad Wagner 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 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 1, '07 at 8:01p
activeJan 4, '07 at 3:02p
posts11
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase