interval madness ...

hello everybody ...

i am sitting here in a training and i am wondering about the
following issue ...

test=# select now() + '3 years 2 decades 4000 seconds 9
minutes'::interval;
?column?
-------------------------------
2031-06-28 11:58:35.052423+02
(1 row)

test=# select now() + '3 years 2 decades 4000 seconds 9 minutes 1
century'::interval;
?column?
-------------------------------
2131-06-28 11:59:01.635835+01
(1 row)

why do i get a different timezone just because of adding one more
century?
i cannot see an obvious reason.

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

"Hans-Juergen Schoenig" <postgres(at)cybertec(dot)at> writes:

> why do i get a different timezone just because of adding one more century?
> i cannot see an obvious reason.

What version of Postgres and what setting of TZ?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

On Jun 28, 2008, at 11:39 AM, Gregory Stark wrote:

> "Hans-Juergen Schoenig" <postgres(at)cybertec(dot)at> writes:
>
>> why do i get a different timezone just because of adding one more
>> century?
>> i cannot see an obvious reason.
>
> What version of Postgres and what setting of TZ?
>
> --
> Gregory Stark
> EnterpriseDB http://www.enterprisedb.com
> Ask me about EnterpriseDB's Slony Replication support!
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

i am on OS X.

test=# select now();
now
------------------------------
2008-06-28 11:42:58.59121+02
(1 row)

test=# select version();

version
------------------------------------------------------------------------
-----------------------------------------------------------------
PostgreSQL 8.3.0 on i386-apple-darwin8.11.1, compiled by GCC i686-
apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build 5370)
(1 row)

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

"Hans-Juergen Schoenig" <postgres(at)cybertec(dot)at> writes:

> why do i get a different timezone just because of adding one more century?
> i cannot see an obvious reason.

This thread may be enlightening:

http://archives.postgresql.org/pgsql-patches/2007-09/msg00292.php

I can't find the message for the later commits but they weren't backpatched to
8.3 so unless you're using HEAD you won't get properly working timezones for
post-2038

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Hans-Juergen Schoenig" <postgres(at)cybertec(dot)at> writes:
>> why do i get a different timezone just because of adding one more century?
>> i cannot see an obvious reason.

> This thread may be enlightening:

> http://archives.postgresql.org/pgsql-patches/2007-09/msg00292.php

> I can't find the message for the later commits but they weren't
> backpatched to 8.3 so unless you're using HEAD you won't get properly
> working timezones for post-2038

Yeah, that patch did get in earlier this year:

2008-02-16 16:16 tgl

* src/: test/regress/expected/timestamptz.out,
test/regress/sql/timestamptz.sql, timezone/README,
timezone/ialloc.c, timezone/localtime.c, timezone/pgtz.c,
timezone/pgtz.h, timezone/private.h, timezone/scheck.c,
timezone/strftime.c, timezone/tzfile.h, timezone/zic.c: Update
timezone code to track the upstream changes since 2003. In
particular this adds support for 64-bit tzdata files, which is
needed to support DST calculations beyond 2038. Add a regression
test case to give some minimal confidence that that really works.

Heikki Linnakangas

I believe the behavior now is that the code will assume the last known
DST rule for a zone applies indefinitely far into the future. But in
8.3 and before all times past 2038 are taken as local standard time.

regards, tom lane