473,899 Members | 4,299 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Jun 22 '07
67 4848
Dennis Lee Bieber <wl*****@ix.net com.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.
Jul 1 '07 #31
Martin Gregorie <ma****@see.sig .for.addresswri tes:
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
Jul 2 '07 #32
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"
Jul 2 '07 #33
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 desynchronizati on, and whatever legal primacy they have
is good in only one country.
Jul 3 '07 #34
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
Jul 3 '07 #35
On 2007-07-03 08:57, Richard Heathfield <rj*@see.sig.in validwrote:
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"
Jul 3 '07 #36
Peter J. Holzer said:
On 2007-07-03 08:57, Richard Heathfield <rj*@see.sig.in validwrote:
>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
Jul 3 '07 #37
On Jul 3, 1:10 am, Paul Rubin <http://phr...@NOSPAM.i nvalidwrote:
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 desynchronizati on, 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.

Jul 3 '07 #38
"Peter J. Holzer" wrote:
Richard Heathfield <rj*@see.sig.in validwrote:
.... 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.securityfoc us.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

Jul 3 '07 #39
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
cienceOfTimekee ping.pdf>)

--
Affijn, Ruud

"Gewoon is een tijger."

Jul 4 '07 #40

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
32440
by: Ben | last post by:
I would like to use php to query a database and retrieve a unix timestamp. The problem is that mysql is storing the data in the date format and not a timestamp. I am sure that I can amend my query to format the date returned as a timestamp without having to do the conversion in php. Can someone tell me what to put in my db query?
7
22057
by: Joshua Beall | last post by:
Hi All, Any thoughts on the easiest way to translate a MySQL timestamp (which looks like 20040422090941) to the datetime format (which looks like 2004-04-22 09:09:41). This is just to make it easier for a human to read it. I have thought about splitting it into chunks using something like str_split, then piecing it back together, but it seems like there should be an easier way. And also, I am not running PHP5.
4
7087
by: RT | last post by:
If anyone can help that would be great. I¹m trying to format a timestamp from my MySQL table (sessions) Here¹s the code I¹m using: <?php echo date('D,n-j-y h:i:s a',strtotime($row_rsSessions)); ?> If I give the timestamp a value of 8 I can get the date to work correctly
13
9320
by: perplexed | last post by:
How do you convert a user inputted date to a unix timestamp before insterting it into your database? I have a form, with a textfield for a date that the user inputs in the format mm-dd-yyyy and three dropdow boxes for hours, minutes, and AM/PM. All of these need to be considered together and converted to one Unix Timestamp and then inserted to the MYSQL date field. The type of field is INT (11) so that I can instead of the standard...
0
1224
by: zion1459 | last post by:
Hi, having a problem using timestamps... all timestamps in the database is are made correctly. It's when I try to convert them it goes wrong. now the weird part is, if I just do a date("D M j G:i:s T Y") to print out the servers time it works perfectly... but as soon I include a variable with the timestamp date("D M j G:i:s T Y", $some_var) it doesn't work... it prints out something with year 2038. Been trying timefstr too but with no...
0
5043
by: James Foreman | last post by:
Looking at the documentation on EXPORT, when exporting a date column it will automatically use yyyymmdd. One could choose to use ISO format, but unfortunately the client I'm exporting to can't deal with this and will only accept dd/mm/yyyy. Undaunted, I find that one can set the format when exporting a timestamp. So I thought of casting the date to a timestamp and then exporting with timestamp format set to dd/mm/yyyy. But one isn't...
9
1713
by: pankaj_wolfhunter | last post by:
Hi, I need some clearance on the following questions: 1) Does LOAD command updates indexes defined for a table? 2) Is REPLACE option in the LOAD command a logged operation? Help will be greatly appreciated. TIA
4
16681
by: mghale | last post by:
I have a question that I'm hoping has an easy answer. I'm working in DB2 V8.2 on AIX 5.3 I have a timestamp column (i.e. 4/26/2006 1:02:42.000000 PM) that I want to return in a report from standard SQL. The user does not want to see it as a timestamp. They want to see just a regular date and time. When I convert to CHAR I get back the wrong format (i.e. 2006-05-04-15.56). What I really want back is the
6
34115
by: marc | last post by:
hi im trying to convert Date() into a unix timestamp so i can stick the result into a mysql db, please help!
8
2091
by: kanwal | last post by:
Hi, I have millions of records in my xxxxx table in mysql. And I have a column of time in which I have stored the timestamp using php time() function. Now I wanna write an SQL query to fetch the records either for year (2006) or for month and year (Jan 2006) Currently I had implement this logic:
0
9845
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11276
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10866
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10976
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9671
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8043
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7204
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6082
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4301
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.