Doug Silver <[EMAIL PROTECTED]> writes: > [ why does he get ] test=# select '2003-04-04'::date::timestamptz; timestamptz ------------------------ 2003-04-03 23:59:00-05 (1 row)
Doug was kind enough to give me access to his machine (a FreeBSD 4.6 box) to look into it. The answer is that the timezone tables on this machine seem to have been built with leap second information; this causes the results of localtime() and related operations to diverge from what Postgres is expecting. What actually happens internally is that localtime() returns the value 2003-04-03 23:59:38-05 (22 seconds off the expected result), but we drop the seconds part for reasons mentioned in timestamp2tm(), giving the observed behavior. I believe that 22 seconds is about right for the accumulated number of leap seconds since 1970, so I'm, um, leaping to the conclusion that localtime is doing a leap-second-aware computation. FreeBSD's "man localtime" points out STANDARDS The asctime(), ctime(), difftime(), gmtime(), localtime(), and mktime() functions conform to ISO/IEC 9899:1990 (``ISO C89''), and conform to ISO/IEC 9945-1:1996 (``POSIX.1'') provided the selected local timezone does not contain a leap-second table (see zic(8)). We are expecting the POSIX-specified behavior (no accounting for leap seconds). Not sure if there's anything much we can do about this except to document "don't do that". It seems impractical to make our datetime arithmetic operations cope with leap-second-aware timekeeping. One idea that comes to mind is to test for leap-second-aware behavior (for example, by checking to see that localtime() of a value that should be exactly midnight is exactly midnight) and complain about it if we find we are on a leap-second-using machine. But I'm not sure if it's worth the trouble. I'm also not sure exactly where/when to perform this test --- perhaps when setting a new timezone value? Comments anyone? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]