Doug Silver 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

Search Discussions

  • Doug Silver at Apr 8, 2003 at 12:14 am

    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/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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 5, '03 at 6:10p
activeApr 8, '03 at 12:14a
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Tom Lane: 1 post Doug Silver: 1 post

People

Translate

site design / logo © 2021 Grokbase