Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Portable general timestamp format, not 2038-limited

Reply
Thread Tools

Portable general timestamp format, not 2038-limited

 
 
James Harris
Guest
Posts: n/a
 
      06-22-2007
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

 
Reply With Quote
 
 
 
 
Owen Jacobson
Guest
Posts: n/a
 
      06-22-2007
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
>
> 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


Use your database's TIMESTAMP type (which is a standard SQL type) and
the java.sql.Timestamp class and call it a day. As far as I know the
various perl and python database libraries can cope with SQL
TIMESTAMPs too.

For postgresql, TIMESTAMP is internally an 8-byte value representing a
point in MJD, with millisecond precision and 4713 BC to 5874897 AD
range.

For mysql, TIMESTAMP is internally a time_t; this does give it the
same limitations as time_t on the platform it was compiled on (1970 to
2038 AD and 1 second resolution for 32-bit systems); you may consider
using DATETIME which the various MySQL drivers for different languages
all convert to timestamp equivalents. There are no built-in temporal
types for mysql with resolution better than 1 second, a problem which
is (as far as I know) unique to that database.

 
Reply With Quote
 
 
 
 
Lew
Guest
Posts: n/a
 
      06-22-2007
James Harris wrote:
> 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>).

But according to the same article:
> (It [year zero] is, however, used in the astronomical system and ISO 8601.)


--
Lew
 
Reply With Quote
 
Roger Miller
Guest
Posts: n/a
 
      06-22-2007
On Jun 22, 10:33 am, James Harris <(E-Mail Removed)>
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.
> ...
> Any thoughts on a better way to do this? (Please reply-all. Thanks).
>
> --
> James



My rule of thumb in situations like this is "When in doubt store it as
text". The one format I am pretty sure we will still be able to deal
with in 2039.

- Roger

 
Reply With Quote
 
Paul Rubin
Guest
Posts: n/a
 
      06-22-2007
James Harris <(E-Mail Removed)> writes:
> 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
> ...


There are subtle issues that have been messed up many times. See:

http://cr.yp.to/time.html

particularly the TAI stuff for some info.
 
Reply With Quote
 
James Harris
Guest
Posts: n/a
 
      06-23-2007
On 22 Jun, 23:49, Roger Miller <(E-Mail Removed)> wrote:
....
> My rule of thumb in situations like this is "When in doubt store it as
> text". The one format I am pretty sure we will still be able to deal
> with in 2039.


Interesting. I hadn't thought about using text. It would add to the
storage a bit as each record is otherwise quite short. But this sounds
like a good option and may help - at least while debugging - to see
the raw date and time as digits. I will consider using this, perhaps
as yyyymmddhhmmssttt.

 
Reply With Quote
 
rossum
Guest
Posts: n/a
 
      06-23-2007
On Sat, 23 Jun 2007 13:37:14 -0700, James Harris
<(E-Mail Removed)> wrote:

>On 22 Jun, 23:49, Roger Miller <(E-Mail Removed)> wrote:
>...
>> My rule of thumb in situations like this is "When in doubt store it as
>> text". The one format I am pretty sure we will still be able to deal
>> with in 2039.

>
>Interesting. I hadn't thought about using text. It would add to the
>storage a bit as each record is otherwise quite short. But this sounds
>like a good option and may help - at least while debugging - to see
>the raw date and time as digits. I will consider using this, perhaps
>as yyyymmddhhmmssttt.

You might prefer to use one of the ISO 8601 formats:
yyyymmddThhmmssttt or yyyy-mm-ddThh:mm:ss.ttt

http://www.cl.cam.ac.uk/~mgk25/iso-time.html

rossum

 
Reply With Quote
 
Roedy Green
Guest
Posts: n/a
 
      06-24-2007
On Fri, 22 Jun 2007 13:33:04 -0700, James Harris
<(E-Mail Removed)> wrote, quoted or indirectly quoted
someone who said :

>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


Unix gets in trouble in 2038 only with 32-bit timestamps. Java's
64-bit longs are fine.

If you need code to create timestamps, you can modify parts of BigDate
to run in Perl or Python.
see http://mindprod.com/products1.html#BIGDATE

To get more detailed, just use a unix long timestamp multiplied by
1000 to track in microseconds.

You can use MS nanosecond timestamps. see
http://mindprod.com/products1.html#FILETIMES

just store them as longs in the database. The only catch is ad-hoc
queries won't work with them.

JDBC out the box should be fine.
one of :
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp
BIGINT long

will be what you need.

--
Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com
 
Reply With Quote
 
Robert Maas, see http://tinyurl.com/uh3t
Guest
Posts: n/a
 
      06-25-2007
> From: James Harris <(E-Mail Removed)>
> I have a requirement to store timestamps in a database. ...
> 1) subsecond resolution - milliseconds or, preferably, more detailed


How do you plan to deal with leap seconds?
- Stick to astronomical time, which is absolutely consistent but
which drifts from legal time?
- Stick to legal time (UTC), which stalls by one second from time
to time, causing time-difference calculations to be incorrect by
varying numbers of seconds?
Only after you make *that* crucial decision, will it be reasonable
to consider milliseconds or other sub-second resolution.

As for the representation to store in the DB, somebody suggested
text, and I agree, with one clarification: Stick to US-ASCII, which
has been incorporated into UniCode hence is pretty much guaranteed
to be stable for longer than you care about.
 
Reply With Quote
 
Roedy Green
Guest
Posts: n/a
 
      06-25-2007
On Sun, 24 Jun 2007 18:14:08 -0700, http://www.velocityreviews.com/forums/(E-Mail Removed) (Robert Maas,
see http://tinyurl.com/uh3t) wrote, quoted or indirectly quoted
someone who said :

>- Stick to astronomical time, which is absolutely consistent but
> which drifts from legal time?


depends what you are measuring. IF you are doing astronomy, your
advice would apply. If you are doing payrolls, you want effectively to
pretend the leap seconds never happened, just as Java does.
--
Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Portable general timestamp format, not 2038-limited James Harris Perl Misc 69 07-12-2007 08:43 PM
Portable general timestamp format, not 2038-limited James Harris Python 82 07-12-2007 02:16 PM
Measuring running time in a general + portable way upperclass C Programming 20 04-27-2007 09:47 AM
Portable Python - free portable development environment ! perica.zivkovic@gmail.com Python 7 01-13-2007 11:19 AM
portable (VHDL) vs. non-portable (altera LPM) approaches to signed computations Eli Bendersky VHDL 1 03-01-2006 02:43 PM



Advertisments