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.
 
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)
 
db74=> select cast(timestamptz '1901/12/13' as date);
    date    
------------
 1901-12-13
(1 row)
 
db74=> select cast(timestamptz '1901/12/15' as date);
    date    
------------
 1901-12-15
(1 row)
 
db74=> \q
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

 

 

 



Message


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 tczPassword: 
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)
 
db74=> 
select cast(timestamptz '1901/12/13' as date);    
date    ------------ 1901-12-13(1 
row)
 
db74=> 
select cast(timestamptz '1901/12/15' as date);    
date    ------------ 1901-12-15(1 
row)
 
db74=> 
\qshower 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
Thu, 27 Nov 2003 17:03:51 +1100
Mike Macaskill <M.Macaskill@bom.gov.au>
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

Sat, 29 Nov 2003 08:47:31 +1100 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: Date bug in PG
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

Fri, 28 Nov 2003 17:38:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Date bug in PG 