Portable general timestamp format, not 2038-limited

[Peter J. Holzer]

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



-- 
   _  | 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"