8

I found this representation of a date (and time?) in an SQLite database. I don't recognize it as anything standard like UNIX timestamp, can anybody help me to identify it?

498586831.475129 or 497469155.776235

sometimes however, it is only one number (without the .).

Some context: I expect the value to be a date (and likely also time, probably to the nearest second) which is post 2007 and not in the future.

More info: The SQLite field type is TIMESTAMP

10

The TIMESTAMP values are likely from Apple Core Foundation code based on an epoch of Jan 1, 2001 00:00:00 GMT.

Time Utilities - Core Foundation | Apple Developer Documentation

Core Foundation measures time in units of seconds. The base data type is the CFTimeInterval, which measures the difference in seconds between two times. Fixed times, or dates, are defined by the CFAbsoluteTime data type, which measures the time interval between a particular date and the absolute reference date of Jan 1 2001 00:00:00 GMT.

Wolfram Alpha: January 1, 2001 00:00:00 GMT + 498586831.475129 seconds
Result: 4:20:31 GMT | Wednesday, October 19, 2016

Wolfram Alpha: January 1, 2001 00:00:00 GMT + 497469155.776235 seconds
Result: 5:52:35 GMT | Thursday, October 6, 2016


Prior answer (still relevant)

Without more context, you must look for conversions that make sense.

The integer part is a reasonable number of seconds, and the decimal places could represent microseconds.

Option 1: UNIX Epoch

Wolfram Alpha: 498586831.475129 seconds since unix epoch

Corresponding Gregorian time and date:

4:20:31 pm UTC | Saturday, October 19, 1985

Is a date in 1985 reasonable for that record?

Option 2: Alternate Epoch

Wolfram Alpha: 498586831.475129 seconds ago

Result:

9:43:46 pm EDT | Sunday, July 22, 2001

Perhaps, the field represented seconds since January 1, 2000 or January 1, 2001.

Option 3: Duration

Wolfram Alpha: 498586831.475129 seconds

Unit conversions:

8.3097805245855×10^6 minutes

138496.342076425 hours

5770.680919851 days

15.799587725555 average Gregorian years

Is this amount of time relevant to the record?

EDIT: With your additional context (of the date being between 2007 and the present), "Option 2: Alternate Epoch" becomes the most reasonable, and this record occurs about 15.8 years afterwards.

  • It is indeed from an iOS app, well spotted :) – Xaser May 10 '17 at 20:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.