Timestamp column in iOS SQLite database

14 Apr

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;

1	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;

1	SELECT strftime('%d - %m - %Y ', datetime(ZONLINEDATE+978307200, 'unixepoch', 'localtime')) FROM ZZPContentModel;

That looks better:

Please consider donating something (even a small amount is ok) to support this site and my work:

Filed under: iPhone

2 Responses for "Timestamp column in iOS SQLite database"

miho November 9th, 2015 at 7:43 1
OMG !! finally found a working solution . thank you so much . really useful info !!
2i3r May 17th, 2017 at 22:48 2
I was looking for it, thank you.
for excel i wrote:
= IF(x, (x/1000/86400) + DATEVALUE(“1-1-2001”), “”)