Обсуждение: Date bug in PG

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