Thread: Date bug in PG
Hi
We are attempting to setup a database application on a Linux box, but we are having
problems loading data with particular dates.
My colleague, Arnold Mavromatis has already made postings to the pgsql-bugs list,
but we are still no closer to having the bug fixed, so I thought I'd email you guys in the
hope we might get the ball rolling on this issue.
We have tested the following configurations:
Linux 2.4.20, PG 7.3.2 (this is the box where we wish to run our application)
HP-UX 11.11, PG 7.3.2, 7.3.4, 7.4 (this is just a test box where PG installs take less time)
On all of these configurations the the date '1901/12/14' apparently does not exist. For example,
using PG 7.4 on HP-UX 11.11:
shower 40: /adamdb/postgres/pg_admin/pgsql_7.4/bin/psql -d db74 -U tcz
Password:
Welcome to psql 7.4, the PostgreSQL interactive terminal.
Password:
Welcome to psql 7.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
db74=> select cast(timestamptz '1901/12/14' as date);
date
------------
1901-12-13
(1 row)
date
------------
1901-12-13
(1 row)
db74=> select cast(timestamptz '1901/12/13' as date);
date
------------
1901-12-13
(1 row)
date
------------
1901-12-13
(1 row)
db74=> select cast(timestamptz '1901/12/15' as date);
date
------------
1901-12-15
(1 row)
date
------------
1901-12-15
(1 row)
db74=> \q
shower 41:
shower 41:
I can give you other examples, but these have already been posted to the pgsql-bugs list by Arnold.
We understand that this problem is related to the timezone and only affects users in our part of the world
(Melbourne, Australia). Is this correct?
If you could shed any light on this and give us some indication of whether or not this big can be fixed,
and/or when it can be fixed it would be of great interest to us.
Thanks,
Mike Macaskill
Mike Macaskill
NCC Computing Support
Tel 9669 4265 Fax 9669 4760
On Thu, 27 Nov 2003, Mike Macaskill wrote: > Linux 2.4.20, PG 7.3.2 (this is the box where we wish to run our > application) > HP-UX 11.11, PG 7.3.2, 7.3.4, 7.4 (this is just a test box where PG > installs take less time) > > On all of these configurations the the date '1901/12/14' apparently does not > exist. For example, > using PG 7.4 on HP-UX 11.11: > > shower 40: /adamdb/postgres/pg_admin/pgsql_7.4/bin/psql -d db74 -U tcz > Password: > Welcome to psql 7.4, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help on internal slash commands > \g or terminate with semicolon to execute query > \q to quit > > db74=> select cast(timestamptz '1901/12/14' as date); > date > ------------ > 1901-12-13 > (1 row) Oh no. I can reproduce this problem using the Australian east coast timezones. The problem is caused by the casting of the date to a timestamp with time zone. What is happening is this: 1901-12-13 with Australian timezone gives us this in DetermineLocalTimeZone(): $4 = -2147472000 (gdb) print *tx $5 = {tm_sec = 0, tm_min = 0, tm_hour = 10, tm_mday = 14, tm_mon = 11, tm_year = 1, tm_wday = 6, tm_yday = 347, tm_isdst= 0, tm_gmtoff = 36000, tm_zone = 0x82fbb90 "EST"} The gmt offset is 36000 seconds = +10 hours. Which is correct. We then compute the offset ourselves (presumably not every platform has tm_gmtoff). This comes out as a negative. We then subtract this from $4 in case we're right on a timezone boundary. In theory, this shouldn't affect dates, since the timezone change is not going to be +/-24, but the code is used for timestamps which may include hours, minutes, seconds, etc. Once we subtract 36000 from $4, we're screwed, since: $4 < - 2^31. This means we have the following: $11 = 2147459296 (gdb) print *tx $12 = {tm_sec = 16, tm_min = 28, tm_hour = 7, tm_mday = 19, tm_mon = 0, tm_year = 138, tm_wday = 2, tm_yday = 18, tm_isdst= 1, tm_gmtoff = 39600, tm_zone = 0x82fbb90 "EST"} Which is obviously wrong, since 1901 != 2138 (see tm_year + 1900). What to do? Well, as far as I can tell, there are no work arounds (do you really need to cast the date to a timestamp with timezone, then to a date?). As for fixing the code, DetermineLocalTimeZone will presumably need to be made to support the range of dates which timestamps support. Gavin
Gavin Sherry <swm@linuxworld.com.au> writes: > What to do? Well, as far as I can tell, there are no work arounds This was discussed a few months ago and set aside because no one had a really decent solution at the time. The behavior is not really all that different from the discontinuities that occur around a daylight-saving transition, but people are used to those because (a) they happen every year, and (b) the bizarreness only lasts an hour and doesn't (with most DST rules) affect local midnight. The discontinuities in apparent local time at the ends of the 32-bit-time_t interval are larger and harder to miss, especially for those of you half a world away from Greenwich. The best thing I have been able to think of is to eliminate these discontinuities by changing our existing definition that says "all times outside the time_t interval (1901 to 2038 at present) are taken as GMT". We could instead define times before the interval as having the same local time offset as prevailed at the start of the interval, and likewise times after the interval have the latest time offset we can determine within the interval. Then there is no DST-like discontinuity in local time at either end of the interval. This might be too big a change in behavior though. Also, if there's anyone whose local timezone database starts in DST mode, it might seem odd for all times before 1901 to look like DST rather than local standard time. Thoughts? Note that this isn't directly connected to the idea of eliminating our dependence on the standard libc timezone routines. If we rolled our own, we'd still need to define what the behavior is outside the range of dates for which we have timezone database entries. But I suspect we'd settle on something more nearly like the above than like the existing behavior... regards, tom lane