Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Perl Misc (http://www.velocityreviews.com/forums/f67-perl-misc.html)
-   -   Portable general timestamp format, not 2038-limited (http://www.velocityreviews.com/forums/t903580-portable-general-timestamp-format-not-2038-limited.html)

James Harris 06-22-2007 08:33 PM

Portable general timestamp format, not 2038-limited
 
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


Lew 06-22-2007 09:38 PM

Re: Portable general timestamp format, not 2038-limited
 
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

Roger Miller 06-22-2007 10:49 PM

Re: Portable general timestamp format, not 2038-limited
 
On Jun 22, 10:33 am, James Harris <james.harri...@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.
> ...
> 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


Paul Rubin 06-22-2007 11:51 PM

Re: Portable general timestamp format, not 2038-limited
 
James Harris <james.harris.1@googlemail.com> 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.

James Harris 06-23-2007 08:37 PM

Re: Portable general timestamp format, not 2038-limited
 
On 22 Jun, 23:49, Roger Miller <roger.mil...@nova-sol.com> 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.


rossum 06-23-2007 10:04 PM

Re: Portable general timestamp format, not 2038-limited
 
On Sat, 23 Jun 2007 13:37:14 -0700, James Harris
<james.harris.1@googlemail.com> wrote:

>On 22 Jun, 23:49, Roger Miller <roger.mil...@nova-sol.com> 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


Roedy Green 06-24-2007 09:13 AM

Re: Portable general timestamp format, not 2038-limited
 
On Fri, 22 Jun 2007 13:33:04 -0700, James Harris
<james.harris.1@googlemail.com> 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

Robert Maas, see http://tinyurl.com/uh3t 06-25-2007 01:14 AM

Re: Portable general timestamp format, not 2038-limited
 
> From: James Harris <james.harri...@googlemail.com>
> 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.

Roedy Green 06-25-2007 11:17 AM

Re: Portable general timestamp format, not 2038-limited
 
On Sun, 24 Jun 2007 18:14:08 -0700, rem642b@yahoo.com (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

Steve O'Hara-Smith 06-25-2007 12:38 PM

Re: Portable general timestamp format, not 2038-limited
 
On Mon, 25 Jun 2007 11:17:27 GMT
Roedy Green <see_website@mindprod.com.invalid> wrote:

> On Sun, 24 Jun 2007 18:14:08 -0700, rem642b@yahoo.com (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.


Which leaves you about 30 seconds out by now - smelly.

--
C:>WIN | Directable Mirror Arrays
The computer obeys and wins. | A better way to focus the sun
You lose and Bill collects. | licences available see
| http://www.sohara.org/


All times are GMT. The time now is 01:26 PM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.