Portable general timestamp format, not 2038-limited

Discussion in 'Perl Misc' started by James Harris, Jun 22, 2007.

  1. James Harris

    James Harris Guest

    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, Jun 22, 2007
    #1
    1. Advertising

  2. James Harris

    Lew Guest

    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
    Lew, Jun 22, 2007
    #2
    1. Advertising

  3. James Harris

    Roger Miller Guest

    On Jun 22, 10:33 am, 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.
    > ...
    > 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
    Roger Miller, Jun 22, 2007
    #3
  4. James Harris

    Paul Rubin Guest

    James Harris <> 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.
    Paul Rubin, Jun 23, 2007
    #4
  5. James Harris

    James Harris Guest

    On 22 Jun, 23:49, Roger Miller <> 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.
    James Harris, Jun 23, 2007
    #5
  6. James Harris

    rossum Guest

    On Sat, 23 Jun 2007 13:37:14 -0700, James Harris
    <> wrote:

    >On 22 Jun, 23:49, Roger Miller <> 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
    rossum, Jun 23, 2007
    #6
  7. James Harris

    Roedy Green Guest

    On Fri, 22 Jun 2007 13:33:04 -0700, James Harris
    <> 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
    Roedy Green, Jun 24, 2007
    #7
  8. > From: James Harris <>
    > 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.
    Robert Maas, see http://tinyurl.com/uh3t, Jun 25, 2007
    #8
  9. James Harris

    Roedy Green Guest

    On Sun, 24 Jun 2007 18:14:08 -0700, (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
    Roedy Green, Jun 25, 2007
    #9
  10. On Mon, 25 Jun 2007 11:17:27 GMT
    Roedy Green <> wrote:

    > On Sun, 24 Jun 2007 18:14:08 -0700, (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/
    Steve O'Hara-Smith, Jun 25, 2007
    #10
  11. Steve O'Hara-Smith wrote:
    > On Mon, 25 Jun 2007 11:17:27 GMT
    > Roedy Green <> wrote:
    >
    >> On Sun, 24 Jun 2007 18:14:08 -0700, (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.
    >

    Easy solution: always read Zulu time directly from a recognized
    real-time clock and store the result in a database as a
    ccyymmddhhmmssfff ASCII string where fff is milliseconds). By
    "recognized real-time clock) that I mean an atomic clock and
    distribution network such as GPS or (in the UK or Germany) an MSF low
    frequency radio broadcast. NTP using tier-1 sources may do the job too.
    The clock interface may need to be JINI because most suitable receivers
    have serial interfaces.

    This is certainly accurate for financial transactions: the UK CHAPS
    inter-bank network has a Rugby MSF receiver in each bank's gateway
    computer and uses that for all timestamps.






    --
    martin@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
    Martin Gregorie, Jun 25, 2007
    #11
  12. James Harris

    James Harris Guest

    On 25 Jun, 02:14, (Robert Maas, see http://tinyurl.com/uh3t)
    wrote:
    > > From: James Harris <>
    > > 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.


    Not a problem for me. I will be taking samples and storing either
    point samples or averages depending on the value being sampled. Pseudo-
    GMT will be good enough. Astronimical time will be no good as the time
    is to relate to the time of day the samples were taken. I think I can
    just use the time as returned by the language I am using (which
    presumably will get it from a C system call or similar). If one sample
    over midnight when a leap second adjustment happens turns out to be
    slightly incorrect it won't skew the results significantly. I could
    sanity check the time, though. Hmmm.....
    James Harris, Jun 25, 2007
    #12
  13. James Harris

    Paul Rubin Guest

    Martin Gregorie <> writes:
    > >> pretend the leap seconds never happened, just as Java does.

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

    > Easy solution: always read Zulu time directly from a recognized
    > real-time clock


    That's no good, it doesn't let you accurately compute the difference
    between timestamps. Nixon resigned the US presidency at noon EDT
    (1800 UTC, I think) on August 9, 1974. You cannot accurately compute
    the number of seconds between Nixon's resignation and 1800 UTC today,
    unless you take into account the leap seconds have been occurred
    between then and now. If you want a precise timestamp and you don't
    want to deal with leap seconds, TAI is one approach. There is
    currently some political pressure to get rid of leap seconds to ease
    computer synchronization, but (at least some of) the astronomy
    community is opposed; see

    http://en.wikipedia.org/wiki/Leap_second
    http://www.ucolick.org/~sla/leapsecs/

    TAI really does seem like the most absolute--if you are a user in
    orbit or on Mars, then UTC timestamps will seem pretty meaningless and
    artificial.

    > By "recognized real-time clock) that I mean an atomic clock and
    > distribution network such as GPS or (in the UK or Germany) an MSF
    > low frequency radio broadcast. NTP using tier-1 sources may do the
    > job too. The clock interface may need to be JINI because most
    > suitable receivers have serial interfaces.


    No do NOT use stratum 1 sources for something like this. They are
    reference clocks for stratum 2 servers and are overloaded from being
    used unnecessarily for other purposes. You are fine using GPS or one
    of the many public lower stratum servers for just about any purpose.
    See:

    http://support.ntp.org/bin/view/Servers/RulesOfEngagement

    > This is certainly accurate for financial transactions: the UK CHAPS
    > inter-bank network has a Rugby MSF receiver in each bank's gateway
    > computer and uses that for all timestamps.


    That is much more sensible than using a stratum 1 server.
    Paul Rubin, Jun 26, 2007
    #13
  14. James Harris

    Guest

    On Jun 25, 6:46 pm, Paul Rubin <http://> wrote:
    > TAI really does seem like the most absolute--if you are a user in
    > orbit or on Mars, then UTC timestamps will seem pretty meaningless and
    > artificial.


    TAI makes sense for clocks on the surface of the earth (at least until
    ion trap clocks and picosecond intercomparison become routine, at
    which point not even TAI tells what time it is for you), but clocks
    off the surface of the earth tick at rates which already differ
    nonlinearly from TAI by measurable amounts.
    , Jun 26, 2007
    #14
  15. Paul Rubin wrote:
    > Martin Gregorie <> writes:
    >>>> pretend the leap seconds never happened, just as Java does.
    >>> Which leaves you about 30 seconds out by now - smelly.

    >> Easy solution: always read Zulu time directly from a recognized
    >> real-time clock

    >
    > That's no good, it doesn't let you accurately compute the difference
    > between timestamps.
    >

    I don't recall the OP mentioning time interval computability - just a
    requirement for sub second accuracy timestamps.

    > If you want a precise timestamp and you don't
    > want to deal with leap seconds, TAI is one approach.
    >

    TAI? Care to provide a reference?

    > There is
    > currently some political pressure to get rid of leap seconds to ease
    > computer synchronization, but (at least some of) the astronomy
    > community is opposed; see
    >

    Yes, that's just silly, especially because if you're trying to do
    date-time calculations across historic time or non-western calendars
    (e.g. Islamic) the minuscule accumulated leap second error is dwarfed by
    all the other uncertainties.

    > No do NOT use stratum 1 sources for something like this.
    >

    Fair comment. I was thinking about network delays and jitter and should
    not have forgotten Stratum 1 congestion. Of course, you could always run
    your own local Stratum 1 clock if accuracy is that important.

    IIRC the major American interbank networks use GPS as their time
    standard because its about the only system that can avoid jitter and
    propagation delays over continental areas without introducing smoothing
    engines, e.g. ntpd.


    --
    martin@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
    Martin Gregorie, Jun 26, 2007
    #15
  16. James Harris

    Paul Rubin Guest

    Martin Gregorie <> writes:
    > I don't recall the OP mentioning time interval computability - just a
    > requirement for sub second accuracy timestamps.


    That Y2038 is an issue suggests the OP wants a timestamp format that
    is future-proof and that means it should be good for all plausible
    applications. That would include computing intervals.

    > > If you want a precise timestamp and you don't
    > > want to deal with leap seconds, TAI is one approach.
    > >

    > TAI? Care to provide a reference?


    Same one already given: http://cr.yp.to/proto/utctai.html
    Paul Rubin, Jun 26, 2007
    #16
  17. Dennis Lee Bieber, Jun 26, 2007
    #17
  18. Paul Rubin wrote:
    > Same one already given: http://cr.yp.to/proto/utctai.html

    <picky_mode>
    Nope - you referenced leap seconds, not TAI and not that URL
    </picky_mode>

    Thanks for the reference, though.


    --
    martin@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
    Martin Gregorie, Jun 26, 2007
    #18
  19. wrote:
    > On Jun 25, 6:46 pm, Paul Rubin <http://> wrote:
    >> TAI really does seem like the most absolute--if you are a user in
    >> orbit or on Mars, then UTC timestamps will seem pretty meaningless and
    >> artificial.

    >
    > TAI makes sense for clocks on the surface of the earth (at least until
    > ion trap clocks and picosecond intercomparison become routine, at
    > which point not even TAI tells what time it is for you), but clocks
    > off the surface of the earth tick at rates which already differ
    > nonlinearly from TAI by measurable amounts.
    >

    True. The first direct demonstration of relativistic time dilation was
    made in 1971 with three HP cesium beam atomic clocks. One stayed in the
    lab, while the other were shipped round the world in opposite directions
    on commercial jet flights. When the clocks were compared afterwards
    the errors in the traveling clocks agreed with theory within
    experimental error. See:

    http://hyperphysics.phy-astr.gsu.edu/hbase/relativ/airtim.html

    for more detail. This shows the clocks don't have to be moving at
    interplanetary speeds to be significantly affected.


    --
    martin@ | Martin Gregorie
    gregorie. | Essex, UK
    org |
    Martin Gregorie, Jun 26, 2007
    #19
  20. James Harris

    Paul Rubin Guest

    Martin Gregorie <> writes:
    > > Same one already given: http://cr.yp.to/proto/utctai.html

    > <picky_mode>
    > Nope - you referenced leap seconds, not TAI and not that URL


    Oh whoops, I thought I put that url further up in the thread.
    I remember grumbling to myself about having to look for it twice.
    Maybe I'm just confused. Anyway it's pretty interesting stuff,
    as is the Wikipedia article someone else linked to.
    Paul Rubin, Jun 26, 2007
    #20
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Stu

    Normalizing tm structure past 2038

    Stu, Oct 31, 2003, in forum: C Programming
    Replies:
    5
    Views:
    421
    Chris Torek
    Nov 1, 2003
  2. James Harris
    Replies:
    82
    Views:
    1,340
  3. James Harris
    Replies:
    77
    Views:
    2,277
  4. islamelnaggar
    Replies:
    4
    Views:
    2,955
    islamelnaggar
    Feb 24, 2008
  5. islamelnaggar
    Replies:
    0
    Views:
    462
    islamelnaggar
    Feb 24, 2008
Loading...

Share This Page