I was researching a database from an iOS app called <appname>.sqlite. From the filename it was obvious that we were dealing with an SQLite database.

I opened the database with SQLite Database Browser and the table I looked at has datetime values which are expressed in the TIMESTAMP data format in SQLite:


SQLite documentation indicates that the TIMESTAMP format is based on unix time: the number of seconds elapsed since 01-01-1970 in UTC time.

I tried to convert the TIMESTAMP to a formatted, local time, string like this with the following query:

But the results were a little odd since I expected dates in 2013:


Apple documentation states that iOS data/time values are stored as a time value relative to an absolute reference date-the first instant of 1 January 2001.

If we calculate the unix time for 01-01-2001 the result is 978307200:


So to get the correct date we can use the following query:

That looks better: