Portable general timestamp format, not 2038-limited

[James Harris]

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

--
James

[James Harris]

On 5 Jul, 02:53, greg <[e-mail]:

James Harris wrote:

With that the time would range to +/- 9000
quintillion years (18 digits)

Use the Big Bang as the epoch, and you won't have
to worry about negative timestamps.

Good idea if only they didn't keep shifting the femtosecond on which
it happened...... :-)

[James Harris]

On 5 Jul, 08:46, Dennis Lee Bieber <[e-mail]:

On Wed, 04 Jul 2007 22:12:46 -0400, Roy Smith <[e-mail]
the following in comp.lang.python:

Astronomers use Julian Date (http://en.wikipedia.org/wiki/Julian_date) for
calculations like this. It's a widely used format and highly portable.
I'm sure there are libraries to deal with it in all the languages you
mention (and more). Ask on sci.astro for more information.

<playing devils advocateBut do you also need to account for
Besselian or Julian centuries (Astronomy used to use B1900 as a
computational epoch, but now uses J2000. A Julian century is 36525 days,
Besselian century was 36524.22 days.

Whew! It was for reasons such as this that I suggested treating a day
(i.e. a /nominal/ 24-hour period) as the primary unit. The Gregorian
switch to Julian, for example, missed out a bunch of days to adjust
the calendars of Christendom but they had to be whole numbers of days.
In terms of real people (about the level I need) once a dividing line
has been chosen between one day and the next it becomes a reference
point.

Incidentally I have chosen to store /average/ values in the
application so if the sample period is 10 seconds and the count
increases by 45 I will store 4.5. This is plottable directly and I
could even allow an 11 second sample when a leap second is added (if I
needed that detail).

Is your Julian century a bit long, on average, 2000, 2400, 2800 etc
having 28 days in Feb?

[Ilya Zakharevich]

[A complimentary Cc of this posting was sent to
James Harris
<ja************@googlemail.com>], who wrote in article <11**********************@m36g2000hse.googlegroups .com>:

On 5 Jul, 02:53, greg <[e-mail]:

James Harris wrote:

With that the time would range to +/- 9000
quintillion years (18 digits)

Use the Big Bang as the epoch, and you won't have
to worry about negative timestamps.

In pedantic mode: negative timestamps make sense with Big Bang as the
epoch as well. (AFAIU, the current way of thinking is that it was
"just too hot" before the big bang, it is not that "there was
nothing".)

Hope this helps,
Ilya

[Wojtek]

James Harris 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

2) not bounded by Unix timestamp 2038 limit

I use the Java Calendar class for storing dates, which as I understand
it, uses a long to store the date/time/milliseconds.

In my application I use the date 9999.12.31 23:59:59.000 to store a
blank date. Calendar has no problem storing that date, and returning
the correct year, month, day, hour, minute, and second.

Note: Since I am using the year 9999 as a "magic number", some of you
may think that I am repeating the Y2K problem. Hey, if my application
is still being used in the year 9998 I am not being paid nearly
enough...

--
Wojtek :-)

[Gabriel Genellina]

En Thu, 05 Jul 2007 17:57:32 -0300, Wojtek <no*****@a.comescribió:

Note: Since I am using the year 9999 as a "magic number", some of you
may think that I am repeating the Y2K problem. Hey, if my application
is still being used in the year 9998 I am not being paid nearly
enough...

I would not say the code itself, but some design decisions may survive for
a long time. Like the railroad inter-rail distance, which even for the
newest trains, remains the same as used by Stephenson in England two
centuries ago - and he choose the same width as used by common horse carts
at the time. (Some people goes beyond that and say it was the same width
as used in the Roman empire but this may be just a practical coincidence,
no causality being involved).

--
Gabriel Genellina

[Steve Holden]

Gabriel Genellina wrote:

En Thu, 05 Jul 2007 17:57:32 -0300, Wojtek <no*****@a.comescribió:

>Note: Since I am using the year 9999 as a "magic number", some of you
may think that I am repeating the Y2K problem. Hey, if my application
is still being used in the year 9998 I am not being paid nearly
enough...

I would not say the code itself, but some design decisions may survive for
a long time. Like the railroad inter-rail distance, which even for the
newest trains, remains the same as used by Stephenson in England two
centuries ago - and he choose the same width as used by common horse carts
at the time. (Some people goes beyond that and say it was the same width
as used in the Roman empire but this may be just a practical coincidence,
no causality being involved).

Brunel, of course, being an original, built his system with a wider
inter-rail gap, and passengers commented on the smoother ride they got.
But standardization wars aren't new, and IKB lost that one.

regards
Steve
--
Steve Holden [phone] [phone]
Holden Web LLC/Ltd http://www.holdenweb.com
Skype: holdenweb http://del.icio.us/steve.holden
--------------- Asciimercial ------------------
Get on the web: Blog, lens and tag the Internet
Many services currently offer free registration
----------- Thank You for Reading -------------

[greg]

Ilya Zakharevich wrote:

In pedantic mode: negative timestamps make sense with Big Bang as the
epoch as well. (AFAIU, the current way of thinking is that it was
"just too hot" before the big bang, it is not that "there was
nothing".)

If Stephen Hawking is right, the shape of the universe
is such that there isn't any time "before" the big bang
at all. It's like asking what's north of the North Pole.

Of course, this may have been replaced with some other
equally bizarre idea by now...

Another thought: If the cosmologists ever decide if
and when the Big Crunch is going to happen, we may be
able to figure out once and for all how many bits we
need in the timestamp.

--
Greg

[Martin Gregorie]

Ilya Zakharevich wrote:

[A complimentary Cc of this posting was sent to
greg
<gr**@cosc.canterbury.ac.nz>], who wrote in article <5f*************@mid.individual.net>:

>Ilya Zakharevich wrote:

>>In pedantic mode: negative timestamps make sense with Big Bang as the
epoch as well. (AFAIU, the current way of thinking is that it was
"just too hot" before the big bang, it is not that "there was
nothing".)

If Stephen Hawking is right, the shape of the universe
is such that there isn't any time "before" the big bang
at all. It's like asking what's north of the North Pole.

I do not remember any statement like this - even from 70s... Could
you provide a reference? There were conjectures about "initial
singularity", but I do not recollect them related to SH.

Its in "A Short History of Time". Sorry I can't quote chapter or page,
but a friend borrowed my copy and lent me Dawkins "Climbing Mount
Improbable" before vanishing, never to be seen since. Not an equal
exchange: I preferred ASHOT to CMI.
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

[Gabriel Genellina]

(Absolutely off topic!)

En Mon, 09 Jul 2007 15:50:58 -0300, Steve Holden <st***@holdenweb.com>
escribió:

Brunel, of course, being an original, built his system with a wider
inter-rail gap, and passengers commented on the smoother ride they got.
But standardization wars aren't new, and IKB lost that one.

And later the Great Western Railway got the most beautiful locomotives of
all times!

I don't know the current status, but some years ago here in Argentina
around 40% of the railroad system were using "wide gauge" = 1676mm, wider
than standard but not as wide as the original GWR.
Now most of the passenger lines are defunct and cargo lines trend to use
narrow gauge = 1000mm so the proportion may be much smaller now.

--
Gabriel Genellina

[Ilya Zakharevich]

[A complimentary Cc of this posting was sent to
Martin Gregorie
<ma****@see.sig.for.address>], who wrote in article <u1************@zoogz.gregorie.org>:

If Stephen Hawking is right, the shape of the universe
is such that there isn't any time "before" the big bang
at all. It's like asking what's north of the North Pole.

I do not remember any statement like this - even from 70s... Could
you provide a reference? There were conjectures about "initial
singularity", but I do not recollect them related to SH.

Its in "A Short History of Time". Sorry I can't quote chapter or page,
but a friend borrowed my copy and lent me Dawkins "Climbing Mount
Improbable" before vanishing, never to be seen since. Not an equal
exchange: I preferred ASHOT to CMI.

I would prefer a reference to a peer-reviewed paper. ;-)

Thanks,
Ilya

[Martin Gregorie]

Ilya Zakharevich wrote:

[A complimentary Cc of this posting was sent to
Martin Gregorie
<ma****@see.sig.for.address>], who wrote in article <u1************@zoogz.gregorie.org>:

>Its in "A Short History of Time". Sorry I can't quote chapter or page,
but a friend borrowed my copy and lent me Dawkins "Climbing Mount
Improbable" before vanishing, never to be seen since. Not an equal
exchange: I preferred ASHOT to CMI.

Oops - I should have written "A Brief History of Time". It was the first
edition, so I don't know if it was altered/edited out of later versions.

I would prefer a reference to a peer-reviewed paper. ;-)

Sure, but I don't think you'll find one. It was in a descriptive, rather
than rigorous, passage. But then, the book famously had only one
equation in it.
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

[Hendrik van Rooyen]

"greg" <gr**@cosc.c...ry.ac.nzwrote:

>
Another thought: If the cosmologists ever decide if
and when the Big Crunch is going to happen, we may be
able to figure out once and for all how many bits we
need in the timestamp.

Unless of course, its all an oscillation - bang, crunch, bang, crunch,
as the cosmic engine ticks over...

But of course, cycles other than our own are kind of unreachable.

- Hendrik

[Ilya Zakharevich]

[A complimentary Cc of this posting was sent to
Martin Gregorie
<ma****@see.sig.for.address>], who wrote in article <qk************@zoogz.gregorie.org>:

Its in "A Short History of Time". Sorry I can't quote chapter or page,
but a friend borrowed my copy and lent me Dawkins "Climbing Mount
Improbable" before vanishing, never to be seen since. Not an equal
exchange: I preferred ASHOT to CMI.

Oops - I should have written "A Brief History of Time". It was the first
edition, so I don't know if it was altered/edited out of later versions.

I would prefer a reference to a peer-reviewed paper. ;-)

Sure, but I don't think you'll find one. It was in a descriptive, rather
than rigorous, passage. But then, the book famously had only one
equation in it.

[I've heard about this book.]

My point is that attributing something to SH due to it appearing in
ABHoT is like attributing it to you since it was mentioned in your
post...

Hope this helps,
Ilya

[Michael Angelo Ravera]

On Jun 22, 1:33 pm, James Harris <[e-mail]>
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)

[Martin Gregorie]

Ilya Zakharevich wrote:

My point is that attributing something to SH due to it appearing in
ABHoT is like attributing it to you since it was mentioned in your
post...

OK, so who should it be attributed to?
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

[Ilya Zakharevich]

[A complimentary Cc of this posting was sent to
Martin Gregorie
<ma****@see.sig.for.address>], who wrote in article <qj************@zoogz.gregorie.org>:

Ilya Zakharevich wrote:

My point is that attributing something to SH due to it appearing in
ABHoT is like attributing it to you since it was mentioned in your
post...

OK, so who should it be attributed to?

*This* was my question; and with kitchentop book, one cannot expect to
find such an answer. If interested, Wiki looks like a good place to
look it up.

Given how obsolete this conjecture is (it contradicts the now known
data about uniformity of background radiation), I have no interest in
looking it up. :-(

Sorry,
Ilya

[Ben Finney]

Ilya Zakharevich <no**********@ilyaz.orgwrites:

*This* was my question; and with kitchentop book, one cannot expect
to find such an answer. If interested, Wiki looks like a good place
to look it up.

I don't know what Wiki[0] has to do with it, but just to check:

<URL:http://c2.com/cgi/wiki?WhatIsNorthOfTheNorthPole>

Nope, can't find any existing page about it.
[0] Left unqualified, referring to a site named "Wiki" refers to *the*
Wiki, at <URL:http://c2.com/cgi/wiki>. If you mean some other
subsequent Wiki site, you'll need to be more specific.

--
\ "Consider the daffodil. And while you're doing that, I'll be |
`\ over here, looking through your stuff." -- Jack Handey |
_o__) |
Ben Finney

[Lew]

Ben Finney wrote:

[0] Left unqualified, referring to a site named "Wiki" refers to *the*
Wiki, at <URL:http://c2.com/cgi/wiki>. If you mean some other
subsequent Wiki site, you'll need to be more specific.

I'm betting they meant
<http://en.wikipedia.org/wiki/Main_Page>

or one of its non-English siblings.

--
Lew