Portable general timestamp format, not 2038-limited

Peter J. Holzer hjp-usenet2 at hjp.at
Tue Jul 3 08:12:20 CEST 2007

On 2007-07-03 05:12, Scott David Daniels <scott.daniels at acm.org> wrote:
> Peter J. Holzer wrote:
>> On 2007-06-22 20:33, James Harris <james.harris.1 at googlemail.com> wrote:
>>> I have a requirement to store timestamps in a database. Simple enough
>>> you might think but finding a suitably general format is not easy. The
>>> specifics are
>> Stick to unix timestamps but store them as a double precision floating
>> point number. The 53 bit mantissa gives you currently a resolution of
>> about 200 ns, slowly deteriorating (you will hit ms resolution in about
>> 280,000 years, if I haven't miscalculated). Any language and database
>> should be able to handle double-precision FP numbers, so that's as
>> portable as it gets and conversion from/to system time should be
>> trivial.
> TOPS-20 did an interesting format which suggest an interesting variant:
>      Tops-20:  36-bit (the machine word size) fixed-bit representation
>                of days since a given moment (the first Photographic
>                plates of the sky).  The "binary point" was at the middle
>                of the word; the low order 18 bits were the time of day
>                (GMT), the high-order 18 bits were the days-since date.
> Inspired format:
>      Days since a some standard date (the TAI date may be a good such
> date) expressed as fixed point 64-bit (32-bit day part, 32-bit
> day-fraction part) or floating point (using Intel's double-precision,
> for example, gives you 26 bits each for day and day-fraction, though
> the binary point moves for particular stamps).

Doesn't MS-Excel store timestamps in such a format?

This requires you to define what a "day" is:

a) 86400 seconds

b) the time between two consecutive readings of 00:00:00 on a UTC clock

c) something else.

Definition b) is probably the most useful.

> Advantages of such a format:
>      Using simple arithmetic for the difference between two such stamps
> is reasonably accurate even without knowing about when the leap seconds
> occur.  Better resolution is available with leap-second aware software.
> A leap second affects the resolution only in intervals where there
> _are_ leap seconds, and ignoring them leaves you almost 5 digits of
> accuracy even when you naively ignore them.

Since a day with a leap second has 86401 seconds (or 86399, but that
hasn't happened yet), a leap second aware counter could record the time
HH:MM:SS on such a day as (HH*3600+MM*60+SS)/86401. If you know that
there was a leap second on that day you can still recover the exact time
wall clock time, otherwise you will be off by up to one second, but the
time is still monotonic and you don't have a sudden jump at the end of
the day.


   _  | Peter J. Holzer    | I know I'd be respectful of a pirate 
|_|_) | Sysadmin WSR       | with an emu on his shoulder.
| |   | hjp at hjp.at         |
__/   | http://www.hjp.at/ |	-- Sam in "Freefall"

More information about the Python-list mailing list