Portable general timestamp format, not 2038-limited

[Michael Angelo Ravera]

On Jun 22, 1:33 pm, James Harris <james.harri... 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
>
> The formats provided by the two database systems are such as 8-byte or
> 12-byte values which, even if I could get Perl to work with I guess it
> would be messy. Keeping to 32-bit values should give me portability
> and be easy enough to work with without obscuring the program logic.
> Since 32 bits of microseconds is less than 50 days I have to store two
> 32-bit values. How to split them? The option I favour at the moment is
> to split days and parts of days like this:
>
> a) store, as a 32-bit number, days since a virtual year zero (there is
> no year zero in common era time <http://en.wikipedia.org/wiki/
> Common_Era>). This allows over five million years plus and minus.
> Still not completely general, I know.
> b) store parts of days as another 32-bit value. Its range would have
> to go to 86401 seconds - the number of seconds in a leap day. This
> means each 'tick' would be around 21 microseconds. For regularity I
> could make the ticks 25 microseconds so there would be 40,000 in a
> second and 3,456,000,000 in a day; and, finally, the counter could
> tick about 5 hours into the next day if not caught.
>
> Any thoughts on a better way to do this? (Please reply-all. Thanks).

The best timestamp of which I am aware is microseconds since the
calendarical convergence back in 4713 BCE. This is the format used on
the old Tandem systems. Tandem chose to begin days at midnight, so
with some small tweaking, you can calculate the Julian day (by adding
12 hours) also, but you can choose to begin them at noon (as the
official Julian day does).

It is easily represented in 64 bits and won't overflow until well past
10000 CE. It also has the advantage of making for easy easy time
arithmetic and for reasonable conversion into any native format with
resolution no better than about 10 nanoseconds. You have to be
careful, if your resolution is better than that (you might overflow a
64-bit number if you try to go to your native format by multiplying
first), but it is quite useful.

The magic number for Unix-32 format is 210866760000000000 (or that
divided by 1000 or 1000000 depending upon which way you go)