Re: [NOVICE] Timestamp with time zone change (error) in 7.3.2?

On Saturday 05 April 2003 10:10 am, Tom Lane wrote:
> Doug Silver <dsilver(at)urchin(dot)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/libtai
for more details. TAI time measure is off 22 seconds from UTC time
measure. Therefore, your system time will show a 22 secs difference
from 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