Re: [NOVICE] Timestamp with time zone change (error) in 7.3.2? - Mailing list pgsql-hackers

On Saturday 05 April 2003 10:10 am, Tom Lane wrote:
> Doug Silver <dsilver@urchin.com> 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

Hi Tom -

The reason for this discrepancy in localtime is due to a program called
clockspeed that is running on the machine that automatically keeps track of
time.  It runs as a daemon and constantly adjusts the clock without the
security concerns of ntpd.  I run it on most of my FreeBSD machines so
that I don't have to worry about syncing the time.

Here is some installation information pertinent to this discussion:Clockspeed uses the libtai library, check
/usr/ports/devel/libtaiformore details. TAI time measure is off 22 seconds from UTC timemeasure. Therefore, your system
timewill show a 22 secs differencefrom your time source after you've installed this port. 

To compensate for this, they suggest compiling a special version of localtime
with that accounted for this: "make -DLEAPSECONDS".  I have seen problems
with clockspeed on an NFS server to non-FBSD clients where they were
complaining about the time, but something must have changed from 7.2 ->7.3 to
cause this problem in the first place.

Thanks for investigating this Tom.

-Doug