Re: 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