473,683 Members | 2,722 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 #1
67 4775
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
Jun 22 '07 #2
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.or g/wiki/Common_Era>).

But according to the same article:
(It [year zero] is, however, used in the astronomical system and ISO 8601.)
--
Lew
Jun 22 '07 #3
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

Jun 22 '07 #4
James Harris <ja************ @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.
Jun 22 '07 #5
On 22 Jun, 23:49, Roger Miller <roger.mil...@n ova-sol.comwrote:
....
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 yyyymmddhhmmsst tt.

Jun 23 '07 #6
On Sat, 23 Jun 2007 13:37:14 -0700, James Harris
<ja************ @googlemail.com wrote:
>On 22 Jun, 23:49, Roger Miller <roger.mil...@n ova-sol.comwrote:
...
>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 yyyymmddhhmmsst tt.
You might prefer to use one of the ISO 8601 formats:
yyyymmddThhmmss ttt or yyyy-mm-ddThh:mm:ss.ttt

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

rossum

Jun 23 '07 #7
On Fri, 22 Jun 2007 13:33:04 -0700, James Harris
<ja************ @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.Timest amp
BIGINT long

will be what you need.

--
Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com
Jun 24 '07 #8
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.
Jun 25 '07 #9
On Sun, 24 Jun 2007 18:14:08 -0700, re*****@yahoo.c om (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
Jun 25 '07 #10

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

Similar topics

2
32422
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
22043
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
7078
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
9291
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
1212
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
5027
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
1699
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
16672
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
34086
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
2069
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
9042
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...
1
8751
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
8771
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7581
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...
0
5789
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
4299
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4519
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2934
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
1921
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.