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

[Carsten Haese]

On Fri, 2007-06-22 at 13:33 -0700, 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

PostgreSQL timestamps do not appear to be limited by Y2K38:

pgtest=create table dt(a timestamp);
CREATE TABLE
pgtest=insert into dt(a) values('2099-01-01 01:23:45.678901');
INSERT 0 1
pgtest=select * from dt;
a
----------------------------
2099-01-01 01:23:45.678901
(1 row)
HTH,

--
Carsten Haese
http://informixdb.sourceforge.net

[Lew]

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]

On Jun 22, 10:33 am, 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.
...
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]

James Harris <ja************@googlemail.comwrites:

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]

On 22 Jun, 23:49, Roger Miller <[e-mail]:
....

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]

On Sat, 23 Jun 2007 13:37:14 -0700, James Harris
<ja************@googlemail.comwrote:

>On 22 Jun, 23:49, Roger Miller <[e-mail]:
...

>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]

On Fri, 22 Jun 2007 13:33:04 -0700, James Harris
<ja************@googlemail.comwrote, 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]

From: James Harris <[e-mail]>

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]

On Sun, 24 Jun 2007 18:14:08 -0700, re*****@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]

On Mon, 25 Jun 2007 11:17:27 GMT
Roedy Green <se*********@mindprod.com.invalidwrote:

On Sun, 24 Jun 2007 18:14:08 -0700, re*****@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/

[Martin Gregorie]

Steve O'Hara-Smith wrote:

On Mon, 25 Jun 2007 11:17:27 GMT
Roedy Green <se*********@mindprod.com.invalidwrote:

>On Sun, 24 Jun 2007 18:14:08 -0700, re*****@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.

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 |

[James Harris]

On 25 Jun, 02:14, [e-mail] (Robert Maas, see http://tinyurl.com/uh3t)
wrote:

From: James Harris <[e-mail]>
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.....

[Paul Rubin]

Martin Gregorie <ma****@see.sig.for.addresswrites:

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/Serv...esOfEngagement

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.

[sla29970]

On Jun 25, 6:46 pm, Paul Rubin <http://[e-mail]:

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.

[Paul Rubin]

Martin Gregorie <ma****@see.sig.for.addresswrites:

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

[Martin Gregorie]

sl******@gmail.com wrote:

On Jun 25, 6:46 pm, Paul Rubin <http://[e-mail]:

>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...iv/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]

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 |

[Paul Rubin]

Martin Gregorie <ma****@see.sig.for.addresswrites:

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.

[Leo Kislov]

On Jun 27, 10:51 pm, Paul Rubin <http://[e-mail]:

The difficulty/impossibility of computing intervals on UTC because of
leap seconds suggests TAI is a superior timestamp format.

If you care about intervals you'd better keep timestamps in SI seconds
since some zero time point (just like OP wanted). TAI timestamps are
pretty useless IMHO. They need to be converted to decimal/float for
interval calculations and they don't represent any legal time.

-- Leo

[sla29970]

On Jun 27, 10:51 pm, Paul Rubin <http://[e-mail]:

According to <http://en.wikipedia.org/wiki/UTC>, UTC is derived from
TAI.

According to <http://en.wikipedia.org/wiki/TAI>, TAI is a proper time,
but the very first section in the TAI discussion page cites a refereed
paper by the person then in charge of TAI which asserts that is not
true.

As for the primacy of UTC vs. TAI, this is the classical chicken and
egg problem. The bureaucratic reality is opposed to the physical
reality.

it's always within 20 nsec. This seems like the kind of correction
that can be applied after the fact.

It is the nature of horology that *all* clocks need corrections
applied after the fact. The question is whether a given clock and its
time distribution system is good enough for the given application.

The difficulty/impossibility of computing intervals on UTC because of leap seconds suggests TAI is a superior timestamp format.

TAI is a superior time scale for processes on the surface of the earth
which only care about nanosecond precision, but it is not practically
available nor legal nor applicable off the surface of the earth. TAI
is itself corrected after the fact by the issue of TT(BIPMxx).

[Peter J. Holzer]

On 2007-06-22 20:33, James Harris <ja************@googlemail.comwrote:

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

Stick to unix timestamps but store them as a double precision floating
point number. The 53 bit mantissa gives you currently a resolution of
about 200 ns, slowly deteriorating (you will hit ms resolution in about
280,000 years, if I haven't miscalculated). Any language and database
should be able to handle double-precision FP numbers, so that's as
portable as it gets and conversion from/to system time should be
trivial.

If you need to represent milliseconds exactly, you can just multiply the
timestamp with 1000 (and get java timestamps).

hp

--
_ | Peter J. Holzer | I know I'd be respectful of a pirate
|_|_) | Sysadmin WSR | with an emu on his shoulder.
| | | hj*@hjp.at |
__/ | http://www.hjp.at/ | -- Sam in "Freefall"

[Roedy Green]

On Tue, 26 Jun 2007 13:04:50 +0100, Martin Gregorie
<ma****@see.sig.for.addresswrote, quoted or indirectly quoted
someone who said :

>TAI? Care to provide a reference?

see http://mindprod.com/jgloss/tai.html
--
Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

[Roedy Green]

On 25 Jun 2007 18:46:25 -0700, Paul Rubin
<http://ph****@NOSPAM.invalidwrote, quoted or indirectly quoted
someone who said :

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

According to Einstein all time is local time, so perhaps our wish for
a clean UT is a pipedream.

To add to the confusion you have GPS, Loran and Julian day also used
as scientific times.
--
Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

[Martin Gregorie]

Roedy Green wrote:

>
To add to the confusion you have GPS, Loran and Julian day also used
as scientific times.

GPS time is UTC time and I'd assume the same is true for Loran. Both are
primarily for navigation and so are on Zulu time, which is another name
for UTC. Zulu is the international radio word for the letter Z.

I've never seen Julian time used outside the world of IBM mainframes.
I'd be interested to know who else uses it.
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

[Martin Gregorie]

Roedy Green wrote:

On Tue, 26 Jun 2007 13:04:50 +0100, Martin Gregorie
<ma****@see.sig.for.addresswrote, quoted or indirectly quoted
someone who said :

>TAI? Care to provide a reference?

see http://mindprod.com/jgloss/tai.html

Thanks.

Your list of NTP servers on http://mindprod.com/jgloss/timesources.html
may be a bit out of date: I notice that it doesn't include the European
or Oceania time server pools (0.europe.pool.ntp.org,
0.oceania.pool.ntp.org). It may be best to just hold a link to the NTP
project servers page, http://support.ntp.org/bin/view/Servers/WebHome
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

[Paul Rubin]

Martin Gregorie <ma****@see.sig.for.addresswrites:

GPS time is UTC time

According to Wikipedia,

While most clocks are synchronized to Coordinated Universal Time
(UTC), the Atomic clocks on the satellites are set to GPS time. The
difference is that GPS time is not corrected to match the rotation of
the Earth, so it does not contain leap seconds or other corrections
which are periodically added to UTC. GPS time was set to match
Coordinated Universal Time (UTC) in 1980, but has since diverged. The
lack of corrections means that GPS time remains at a constant offset
(19 seconds) with International Atomic Time (TAI).

http://en.wikipedia.org/wiki/GPS#Eph...d_clock_errors

[Roedy Green]

On Sun, 01 Jul 2007 17:47:36 +0100, Martin Gregorie
<ma****@see.sig.for.addresswrote, quoted or indirectly quoted
someone who said :

>GPS time is UTC time and I'd assume the same is true for Loran.

not according to this site that has clocks running on all three.
They are out slightly.
http://www.leapsecond.com/java/gpsclock.htm
--
Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

[CBFalconer]

Roedy Green wrote:

On 25 Jun 2007 18:46:25 -0700, Paul Rubin

.... snip ...

>

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

According to Einstein all time is local time, so perhaps our wish
for a clean UT is a pipedream.

To add to the confusion you have GPS, Loran and Julian day also
used as scientific times.

In summary, time is now defined as a non-continuous function, and
is thus proof against manipulation by most standard algebraic
techniques. Take that :-) In fact, it is not even quanticized.

--
<http://www.cs.auckland.ac.nz/~pgut001/pubs/vista_cost.txt>
<http://www.securityfocus.com/columnists/423>
<http://www.aaxnet.com/editor/edit043.html>
cbfalconer at maineline dot net

--
Posted via a free Usenet account from http://www.teranews.com

[Martin Gregorie]

Roedy Green wrote:

On Sun, 01 Jul 2007 17:47:36 +0100, Martin Gregorie
<ma****@see.sig.for.addresswrote, quoted or indirectly quoted
someone who said :

>GPS time is UTC time and I'd assume the same is true for Loran.

not according to this site that has clocks running on all three.
They are out slightly.

http://www.leapsecond.com/java/gpsclock.htm

A useful reference: thanks. Interesting that GPS and Loran are slightly
out from Zulu. I'll ask round and see if this is something that ATP
pilots are aware of: as a glider pilot and GPS user I'd never heard of
it. So far the deviations from UTC are probably not enough to affect
navigation significantly, but I wonder if banks using networks that
timestamp transactions with GPS time know about it. Of course the
deviation can't affect disputes where the timestamps are being used to
decide event sequences within a single network. However, there could be
legal implications if absolute time is important or if the timestamps
are being compared across different networks, e.g SWIFT vs CHAPS or Fedwire.
--
martin@ | Martin Gregorie
gregorie. | Essex, UK
org |

[Paul Rubin]

Dennis Lee Bieber <wl*****@ix.netcom.comwrites:

What is not mentioned is that, as part of the data stream picked up
by GPS receivers, is a term specifying the "correction factor" between
GPS and UTC; so receivers can display UTC time.

Oh yes, good point, the article ought to mention that.

[Ben Finney]

Martin Gregorie <ma****@see.sig.for.addresswrites:

I've never seen Julian time used outside the world of IBM
mainframes. I'd be interested to know who else uses it.

Systems which need to perform date+time computations into the
past. One advantage of Julian time is that it ignores the "1 BC was
immediately followed by 1 AD, there is no 0 AD" hiccup, so Julian time
allows dates to use simple arithmetic to determine the interval.

I know that PostgreSQL at least stores date values in Julian time, for
exactly this benefit.

--
\ "My roommate got a pet elephant. Then it got lost. It's in the |
`\ apartment somewhere." -- Steven Wright |
_o__) |
Ben Finney

[John W. Kennedy]

Martin Gregorie wrote:

Roedy Green wrote:

>>
To add to the confusion you have GPS, Loran and Julian day also used
as scientific times.
>

GPS time is UTC time

No it isn't. GPS has never introduced a leap second, and is still on
uncorrected UTC-as-of-1980. However, the GPS signal also includes an
occasional UTC correction figure, so it can be used to obtain UTC.
--
John W. Kennedy
"The first effect of not believing in God is to believe in anything...."
-- Emile Cammaerts, "The Laughing Prophet"

[Paul Rubin]

sl******@gmail.com writes:

As for the primacy of UTC vs. TAI, this is the classical chicken and
egg problem. The bureaucratic reality is opposed to the physical
reality.

Well, if you're trying to pick just one timestamp standard, I'd say
you're better off using a worldwide one rather than a national one, no
matter how the bureaucracies work. TAI is derived from atomic clocks
all over the world, while the national metrology labs are more subject
to error and desynchronization, and whatever legal primacy they have
is good in only one country.

[Richard Heathfield]

Paul Rubin said:

sl******@gmail.com writes:

>As for the primacy of UTC vs. TAI, this is the classical chicken and
egg problem. The bureaucratic reality is opposed to the physical
reality.

Well, if you're trying to pick just one timestamp standard, I'd say
you're better off using a worldwide one rather than a national one, no
matter how the bureaucracies work.

In that case, the obvious choice is Greenwich Mean Time. :-)

Seriously, GMT is recognised all over the world (far more so, in fact,
than UTC, which tends to be recognised only by some well-educated
people, and there are precious few of those), so why not use it?

I always leave my PC's clock set to GMT, partly out of this desire to
support a single timestamp standard, and (it must be said) partly out
of general cussedness.

--
Richard Heathfield <http://www.cpax.org.uk>
Email: -www. +rjh@
Google users: <http://www.cpax.org.uk/prg/writings/googly.php>
"Usenet is a strange place" - dmr 29 July 1999

[Peter J. Holzer]

On 2007-07-03 08:57, Richard Heathfield <rj*@see.sig.invalidwrote:

Paul Rubin said:

>sl******@gmail.com writes:

>>As for the primacy of UTC vs. TAI, this is the classical chicken and
egg problem. The bureaucratic reality is opposed to the physical
reality.

Well, if you're trying to pick just one timestamp standard, I'd say
you're better off using a worldwide one rather than a national one, no
matter how the bureaucracies work.

In that case, the obvious choice is Greenwich Mean Time. :-)

Hardly. That hasn't been in use for over 35 years (according to
Wikipedia).

Seriously, GMT is recognised all over the world (far more so, in fact,
than UTC, which tends to be recognised only by some well-educated
people, and there are precious few of those), so why not use it?

While the layman may recognize the term "GMT", he almost certainly means
"UTC" when he's talking about GMT. GMT was based on astronomical
observations and the be best approximation available today is probably
UT1, which may differ from UTC by up to 0.5 seconds.

I always leave my PC's clock set to GMT,

Your PC is directly linked to an observatory? Impressive :-). If you
synchronize your PC to any external time source, it's almost certainly
UTC, not GMT or UT1. If you don't synchronize it it's so far off that it
doesn't matter.

hp

--
_ | Peter J. Holzer | I know I'd be respectful of a pirate
|_|_) | Sysadmin WSR | with an emu on his shoulder.
| | | hj*@hjp.at |
__/ | http://www.hjp.at/ | -- Sam in "Freefall"

[Richard Heathfield]

Peter J. Holzer said:

On 2007-07-03 08:57, Richard Heathfield <rj*@see.sig.invalidwrote:

>Paul Rubin said:

>>sl******@gmail.com writes:
As for the primacy of UTC vs. TAI, this is the classical chicken
and
egg problem. The bureaucratic reality is opposed to the physical
reality.

Well, if you're trying to pick just one timestamp standard, I'd say
you're better off using a worldwide one rather than a national one,
no matter how the bureaucracies work.

In that case, the obvious choice is Greenwich Mean Time. :-)

Hardly. That hasn't been in use for over 35 years (according to
Wikipedia).

Nonsense. I use it every day, and have been doing so for - well, rather
more than 35 years.

>Seriously, GMT is recognised all over the world (far more so, in
fact, than UTC, which tends to be recognised only by some
well-educated people, and there are precious few of those), so why
not use it?

While the layman may recognize the term "GMT", he almost certainly
means "UTC" when he's talking about GMT.

Most people of my acquaintance who use the term "GMT" mean precisely
that - Greenwich Mean Time.

<snip>

>I always leave my PC's clock set to GMT,

Your PC is directly linked to an observatory?

Nope. My PC *defines* GMT. If the observatory wants to know what the
exact time is, they only have to ask.

--
Richard Heathfield <http://www.cpax.org.uk>
Email: -www. +rjh@
Google users: <http://www.cpax.org.uk/prg/writings/googly.php>
"Usenet is a strange place" - dmr 29 July 1999

[sla29970]

On Jul 3, 1:10 am, Paul Rubin <http://[e-mail]:

Well, if you're trying to pick just one timestamp standard, I'd say
you're better off using a worldwide one rather than a national one, no
matter how the bureaucracies work. TAI is derived from atomic clocks
all over the world, while the national metrology labs are more subject
to error and desynchronization, and whatever legal primacy they have
is good in only one country.

For the purposes of an operational system there is an important
difference between a time scale which is practically available in real
time and a time scale which is not available until next month. There
is no available source for TAI, and in the current scheme of things
there cannot be one for there is no mechanism for distributing it.

There are two reasonably reliable worldwide time sources right now:
Russia's GLONASS and US GPS. GPS time is based on UTC(USNO).
UTC(USNO) is TA(USNO) minus leap seconds. Note that is TA(USNO), not
TAI(USNO), for USNO cannot define anything named TAI.

[CBFalconer]

"Peter J. Holzer" wrote:

Richard Heathfield <rj*@see.sig.invalidwrote:

.... snip ...

>

>In that case, the obvious choice is Greenwich Mean Time. :-)

Hardly. That hasn't been in use for over 35 years (according to
Wikipedia).

I am glad to see you depend on absolutely reliable sources.

--
<http://www.cs.auckland.ac.nz/~pgut001/pubs/vista_cost.txt>
<http://www.securityfocus.com/columnists/423>
<http://www.aaxnet.com/editor/edit043.html>
cbfalconer at maineline dot net
--
Posted via a free Usenet account from http://www.teranews.com

[Dr.Ruud]

Peter J. Holzer schreef:

Since a day with a leap second has 86401 seconds (or 86399, but that
hasn't happened yet)

Many systems allow a seconds value of 0..61, so minutes (actually
months) with two leap seconds are foreseen.

A leap second may be introduced at the end of any month, the preferred
dates are at the end of June and the end of December.

At the estimated rate of decrease, the earth would lose about 1/2 day
after 4,000 years, and about two leap seconds a
month would be needed to keep UTC in step with Earth time, UT1.

(source:
<URL:http://www.allanstime.com/Publicatio...mekeeping/TheS
cienceOfTimekeeping.pdf>)

--
Affijn, Ruud

"Gewoon is een tijger."

[Peter J. Holzer]

On 2007-07-03 23:15, CBFalconer <cb********@yahoo.comwrote:

"Peter J. Holzer" wrote:

>Richard Heathfield <rj*@see.sig.invalidwrote:

... snip ...

>>

>>In that case, the obvious choice is Greenwich Mean Time. :-)

Hardly. That hasn't been in use for over 35 years (according to
Wikipedia).

I am glad to see you depend on absolutely reliable sources.

Mostly I relied on my own memory (which is of course even less reliable
than Wikipedia). I checked Wikipedia for the date (Jan 1st 1972) when
GMT was replaced by UTC as the basis for civil time. Since that
coincided with my own recollection (sometime in the 1970's), I see no
reason to doubt it.

It is possible that the observatory at Greenwich still keeps and
announces GMT, but it has no practical importance anymore. Certainly
what everybody (except specialists in the field) means when they talk
about "GMT" is UTC.

hp
--
_ | Peter J. Holzer | I know I'd be respectful of a pirate
|_|_) | Sysadmin WSR | with an emu on his shoulder.
| | | hj*@hjp.at |
__/ | http://www.hjp.at/ | -- Sam in "Freefall"

[Peter J. Holzer]

On 2007-07-04 00:14, Dr.Ruud <rv********@isolution.nlwrote:

Peter J. Holzer schreef:

>Since a day with a leap second has 86401 seconds (or 86399, but that
hasn't happened yet)

Many systems allow a seconds value of 0..61, so minutes (actually
months) with two leap seconds are foreseen.

That comes from the ANSI C standard. It is unclear why the standard
specifies 0..61 instead of 0..60. The most plausible explanation I've
read is that it's the result of a misunderstanding: Up to two leap
seconds in a year are expected, and somebody thought they would be
applied both at the end of the year (instead of one at the end of each
semester).

A leap second may be introduced at the end of any month, the preferred
dates are at the end of June and the end of December.

At the estimated rate of decrease, the earth would lose about 1/2 day
after 4,000 years, and about two leap seconds a
month would be needed to keep UTC in step with Earth time, UT1.

C is already ready for this, although I doubt that it's authors planned
that far ahead.

hp
--
_ | Peter J. Holzer | I know I'd be respectful of a pirate
|_|_) | Sysadmin WSR | with an emu on his shoulder.
| | | hj*@hjp.at |
__/ | http://www.hjp.at/ | -- Sam in "Freefall"

[Richard Heathfield]

Peter J. Holzer said:

<snip>

It is possible that the observatory at Greenwich still keeps and
announces GMT, but it has no practical importance anymore. Certainly
what everybody (except specialists in the field) means when they talk
about "GMT" is UTC.

I am not a specialist in the field. When I talk about GMT, I mean GMT,
not UTC. Therefore, I am a counter-example to your claim.

--
Richard Heathfield <http://www.cpax.org.uk>
Email: -www. +rjh@
Google users: <http://www.cpax.org.uk/prg/writings/googly.php>
"Usenet is a strange place" - dmr 29 July 1999

[Ben Finney]

CBFalconer <cb********@yahoo.comwrites:

"Peter J. Holzer" wrote:

Hardly. That hasn't been in use for over 35 years (according to
Wikipedia).

I am glad to see you depend on absolutely reliable sources.

Wikipedia is not an absolutely reliable source. I know of no
"absolutely resliable source". We work with imperfect human-provided
data all the time.

--
\ "If you ever teach a yodeling class, probably the hardest thing |
`\ is to keep the students from just trying to yodel right off. |
_o__) You see, we build to that." -- Jack Handey |
Ben Finney

[James Harris]

On 1 Jul, 15:11, "Peter J. Holzer" <[e-mail]:
....

Stick to unix timestamps but store them as a double precision floating
point number. The 53 bit mantissa gives you currently a resolution of
about 200 ns, slowly deteriorating (you will hit ms resolution in about
280,000 years, if I haven't miscalculated). Any language and database
should be able to handle double-precision FP numbers, so that's as
portable as it gets and conversion from/to system time should be
trivial.

If you need to represent milliseconds exactly, you can just multiply the
timestamp with 1000 (and get java timestamps).

Interesting option. I think my choice is between separate day and sub-
day 32-bit unsigned integers, text, and this 64-bit float option.

I'm not clear, though. Did you mean to store double precision numbers
where the seconds are the units (I assume this) or where the days are
the units? And what do you think of the other option?

[James Harris]

On 3 Jul, 06:12, Scott David Daniels <[e-mail]:
....

Inspired format:
Days since a some standard date (the TAI date may be a good such
date) expressed as fixed point 64-bit (32-bit day part, 32-bit
day-fraction part) or floating point (using Intel's double-precision,
for example, gives you 26 bits each for day and day-fraction, though
the binary point moves for particular stamps).

This is close to or the same as my original suggestion. The break
between days and sub-days seems to make more sense than breaking the
fractional part elsewhere. It also gives a convenient point to hang
datestamps on rather than just timestamps.

FWIW I wonder if a 64-bit version of the above would cope with all
practical time needs. With that the time would range to +/- 9000
quintillion years (18 digits) and there would be over 200 trillion
ticks per second or 200 in a picosecond making, I think, each tick 5
femtoseconds.

[Peter J. Holzer]

On 2007-07-04 18:46, James Harris <ja************@googlemail.comwrote:

On 1 Jul, 15:11, "Peter J. Holzer" <[e-mail]:
...

>Stick to unix timestamps but store them as a double precision floating
point number. The 53 bit mantissa gives you currently a resolution of
about 200 ns, slowly deteriorating (you will hit ms resolution in about
280,000 years, if I haven't miscalculated). Any language and database
should be able to handle double-precision FP numbers, so that's as
portable as it gets and conversion from/to system time should be
trivial.

If you need to represent milliseconds exactly, you can just multiply the
timestamp with 1000 (and get java timestamps).

Interesting option. I think my choice is between separate day and sub-
day 32-bit unsigned integers, text, and this 64-bit float option.

I'm not clear, though. Did you mean to store double precision numbers
where the seconds are the units (I assume this) or where the days are
the units? And what do you think of the other option?

I was thinking about using the seconds as units (so
2007-07-04T23:02:04.123 CET is represented as 1183582924.123).
It's a natural extension of the unix time stamp, so you can often just
pass the values to the normal date routines (especially in languages
like perl which don't really distinguish between integers and floating
point numbers).

But it really doesn't matter much. If you ignore leap seconds, using
days instead of seconds is just a constant factor (in fact, the unix
timestamp ignores leap seconds, too, so it's always a constant factor).
You can't represent a second exactly if the unit is one day (1/86400 is
not a multiple of a power of two), but that probably doesn't matter.
hp
--
_ | Peter J. Holzer | I know I'd be respectful of a pirate
|_|_) | Sysadmin WSR | with an emu on his shoulder.
| | | hj*@hjp.at |
__/ | http://www.hjp.at/ | -- Sam in "Freefall"

[James Harris]

On 4 Jul, 22:18, "Peter J. Holzer" <[e-mail]:
....

But it really doesn't matter much. If you ignore leap seconds, using
days instead of seconds is just a constant factor (in fact, the unix
timestamp ignores leap seconds, too, so it's always a constant factor).
You can't represent a second exactly if the unit is one day (1/86400 is
not a multiple of a power of two), but that probably doesn't matter.

Sure. However, the proposal was to define ticks as 25 microseconds.

[greg]

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.

--
Greg

[Roy Smith]

ames Harris <ja************@googlemail.comwrote:

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

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.