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:
SELECT strftime('%d - %m - %Y ', datetime(ZONLINEDATE, 'unixepoch', 'localtime')) FROM ZZPContentModel;
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:
SELECT strftime('%d - %m - %Y ', datetime(ZONLINEDATE+978307200, 'unixepoch', 'localtime')) FROM ZZPContentModel;
That looks better: