Thread: 8.1.x (tested 8.1.8) timezone bugs
Hello,
When updating for DST we found this nifty problem. This does not happen
on 8.2.
postgres=# SET TIMEZONE TO 'PST8PDT';
SET
postgres=# select now(); now
-------------------------------2007-03-13 10:41:07.034754-07
(1 row)
postgres=# SET TIMEZONE TO PST8PDT;
SET
postgres=# SELECT NOW(); now
-------------------------------2007-03-13 09:41:07.036069-08
(1 row)
postgres=# set time zone PST8PDT;
SET
postgres=# select now(); now
-------------------------------2007-03-13 09:41:07.038045-08
(1 row)
postgres=# SET TIME ZONE 'PST8PDT';
SET
postgres=# select now(); now
-------------------------------2007-03-13 10:41:07.039145-07
(1 row)
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Joshua D. Drake wrote:
> Hello,
>
> When updating for DST we found this nifty problem. This does not happen
> on 8.2.
>
Updated to show timezone:
postgres=# SET TIMEZONE TO 'PST8PDT';
SET
postgres=# select now(); now
-------------------------------2007-03-13 10:44:33.156381-07
(1 row)
postgres=# SHOW TIMEZONE;TimeZone
----------PST8PDT
(1 row)
postgres=# SET TIMEZONE TO PST8PDT;
SET
postgres=# SHOW TIMEZONE;TimeZone
----------pst8pdt
(1 row)
postgres=# SELECT NOW(); now
------------------------------2007-03-13 09:44:33.16286-08
(1 row)
postgres=# set time zone PST8PDT;
SET
postgres=# SHOW time zone;TimeZone
----------pst8pdt
(1 row)
postgres=# select now(); now
-------------------------------2007-03-13 09:44:33.166297-08
(1 row)
postgres=# SET TIME ZONE 'PST8PDT';
SET
postgres=# SHOW TIME ZONE;TimeZone
----------PST8PDT
(1 row)
postgres=# select now(); now
-------------------------------2007-03-13 10:44:38.352985-07
(1 row)
postgres=#
> postgres=# SET TIMEZONE TO 'PST8PDT';
> SET
> postgres=# select now();
> now
> -------------------------------
> 2007-03-13 10:41:07.034754-07
> (1 row)
>
> postgres=# SET TIMEZONE TO PST8PDT;
> SET
> postgres=# SELECT NOW();
> now
> -------------------------------
> 2007-03-13 09:41:07.036069-08
> (1 row)
>
> postgres=# set time zone PST8PDT;
> SET
> postgres=# select now();
> now
> -------------------------------
> 2007-03-13 09:41:07.038045-08
> (1 row)
>
> postgres=# SET TIME ZONE 'PST8PDT';
> SET
> postgres=# select now();
> now
> -------------------------------
> 2007-03-13 10:41:07.039145-07
> (1 row)
>
>
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> When updating for DST we found this nifty problem. This does not happen
> on 8.2.
And your point is?
regards, tom lane
Tom Lane wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> When updating for DST we found this nifty problem. This does not happen >> on 8.2. > > And your point is? Was the test case not clear? Is there something else I should have done? Does this not seem a problem to you? Sincerely, Joshua D. Drake > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Does this not seem a problem to you?
No. 8.2 is case-insensitive on timezone names, but prior releases were not.
I believe the difference you're seeing in 8.1 is that 'PST8PDT' binds to
the zic database entry by that name, while 'pst8pdt' falls back on the
POSIX-default rules, which are presumably still the old DST law.
regards, tom lane
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Does this not seem a problem to you?
>
> No. 8.2 is case-insensitive on timezone names, but prior releases were not.
> I believe the difference you're seeing in 8.1 is that 'PST8PDT' binds to
> the zic database entry by that name, while 'pst8pdt' falls back on the
> POSIX-default rules, which are presumably still the old DST law.
Well in my test case, I explicitly note that PST8PDT != 'PST8PDT' .
That is my actual concern. Before DST it wasn't an issue, now it is. Is
that PostgreSQL's problem? Probably not, but it certainly seems
inconsistent.
Sincerely,
Joshua D. Drake
>
> regards, tom lane
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> That is my actual concern. Before DST it wasn't an issue, now it is. Is
> that PostgreSQL's problem? Probably not, but it certainly seems
> inconsistent.
[ digs around... ] Here we are (in localtime.c):
/** The DST rules to use if TZ has no rules and we can't load TZDEFRULES.* We default to US rules as of 1999-08-17.*
POSIX1003.1 section 8.1.1 says that the default DST rules are* implementation dependent; for historical reasons, US
rulesare a* common default.*/
#define TZDEFRULESTRING ",M4.1.0,M10.5.0"
AFAICS there is not any provision in the code that reads that string to
have different rules in different years, so you're more or less stuck
with being wrong before 2007 or being wrong after. Realistically this
means that POSIX-style rule strings are no longer useful, and you'd
better start using one of the zic timezone names. Which, prior to 8.2,
means you'd better spell PST8PDT in upper case ... or maybe insert a
symlink in the timezone/ tree to make the lower-case name valid ...
regards, tom lane
> AFAICS there is not any provision in the code that reads that string to > have different rules in different years, so you're more or less stuck > with being wrong before 2007 or being wrong after. Realistically this > means that POSIX-style rule strings are no longer useful, and you'd > better start using one of the zic timezone names. Which, prior to 8.2, > means you'd better spell PST8PDT in upper case ... or maybe insert a > symlink in the timezone/ tree to make the lower-case name valid ... O.k., really trying to not be difficult here but... if we modified 8.1.9 (to be released) to not fold the case of the unquoted timezone, doesn't that resolve the issue entirely? (well it is a band-aid but still). Joshua D. Drake > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
"Joshua D. Drake" <jd@commandprompt.com> writes:
> O.k., really trying to not be difficult here but... if we modified 8.1.9
> (to be released) to not fold the case of the unquoted timezone, doesn't
> that resolve the issue entirely? (well it is a band-aid but still).
No, it doesn't; the issue is what happens when there's no match in the
zic database for your TZ name.
Not to mention that we can't realistically put the case-folding change
into a minor release, both for compatibility reasons and because it was
part of a fairly major overhaul of the timezone code. I no longer
recall what-all it was connected to, but there were a number of
interrelated changes there.
Digging around some more in the zic code, I note that zic has an option
"-p" to select a specific known timezone as "posixrules", and that the
fallback code seems to prefer that to the hardwired string. That might
work for us, if what it takes from the zone is just the DST rules and
not the specific offset. Why don't you tryln -s EST5EDT /usr/share/postgresql/timezone/posixrules
(adjust path as needed) and then see how it behaves with 'pst8pdt'?
regards, tom lane