$theTitle=wp_title(" - ", false); if($theTitle != "") { ?>
About Virtualization, VDI, SBC, Application Compatibility and anything else I feel like
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:
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:
1 | SELECT strftime('%d - %m - %Y ', datetime(ZONLINEDATE+978307200, 'unixepoch', 'localtime')) FROM ZZPContentModel; |
That looks better:
2 Responses for "Timestamp column in iOS SQLite database"
OMG !! finally found a working solution . thank you so much . really useful info !!
I was looking for it, thank you.
for excel i wrote:
= IF(x, (x/1000/86400) + DATEVALUE(“1-1-2001”), “”)
Leave a reply