Date/Time stored as floating point, which algorithm is used?

Question

I'm have access to a 3rd party application's database, and I see a field called "date" which stores date/time values as floating point numbers, but I'm not sure how this floating point number is mapped to a date/time. There is no documentation for this database.

Here is some sample data:

date-field        actual-date-time
253507382.168744  1/12/09 6:43 PM PST
253507480.136126  1/12/09 6:44 PM PST
253508091.838982  1/12/09 6:54 PM PST
256703604.015055  2/18/09 6:33 PM PST
256704413.484674  2/18/09 6:46 PM PST

Note: I had to enter these values manually so there's a slight chance they may be off a bit. If you would like to see more data, let me know and I'll add more.

I'm hoping someone is familiar with storing dates in this format and can let me know how to get a date/time given a floating point number.

Answer 1

If you subtract any of the two points you'll see that the values represent the number of seconds, at microsecond accuracy. It should be easy to work out the base date where the clock "started". On Unix and related systems this is January 1st, 1970.

Answer 2

If you look at the change in the numbers over the 10 and 13 minute intervals, you'll see that it's about 60. Therefore I conclude that it's a count of the number of seconds from a base date.

I think the base date is 1/1/2000 or 1/1/2001.

Edit: The base date appears to be 1/1/2001, and the time appears to be adjusted as well - it's probably UTC with your local time offset added.

Answer 3

The timestamps are 'number of seconds elapsed since 00:00 on January 1st 2001'. It's not a common date format but at least it should be easy to work with now you know what it represents!