Portable general timestamp format, not 2038-limited

[Scott David Daniels]

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
>>
>> 1) subsecond resolution - milliseconds or, preferably, more detailed
>> 2) not bounded by Unix timestamp 2038 limit
>> 3) readable in Java
>> 4) writable portably in Perl which seems to mean that 64-bit values
>> are out
>> 5) readable and writable in Python
>> 6) storable in a free database - Postgresql/MySQL
> 
> 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.
> 
> If you need to represent milliseconds exactly, you can just multiply the
> timestamp with 1000 (and get java timestamps).
> 
> 	hp

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).

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.

Disadvantages:
     time-of-day is not simple (but I maintain it shouldn't be).
     No external way to know if a stamp is leap-second aware or not;
you'll just have to know for a whole group.
     Once you have done a naive difference, there is no way to correct it.


--Scott David Daniels
scott.daniels at acm.org