Peter J. Holzer wrote:
> On 2007-06-22 20:33, James Harris <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]
--
http://mail.python.org/mailman/listinfo/python-list
