Search code examples
iossqlsqliteitunes

Formatting date from INTEGER field in SqLite database (iTunes backup)

I am looking at my yesterday iPhone backup file using SQLite Maestro, and the date field shows INTEGER numbers - e.g. the date of my last SMS message shows as 404076049. How do I convert it to a readable datetime? I found somewhere an example SELECT strftime("%m-%d-%Y", date_col, 'unixepoch') AS date_col but that convert the aforementioned number to 12-31-1982, which is definitely wrong (date should be some time yesterday)

Any ideas?

Solution

  • The Mac uses a different "epoch time" than the UNIX epoch time.

    The UNIX epoch date is Midnight January 1, 1970 UTC (aka Greenwich Mean Time)

    The Mac OS epoch date is Midnight January 1, 2001 UTC.

    I don't know how to convert a date from integer format to date string using SQL statements, but I do know how to do it using Cocoa:

    NSDate *date = [NSDate dateWithTimeIntervalSinceReferenceDate: intValue];

    You should be able to calculate the difference between the 2 date systems and apply that as a conversion factor:

    NSDate *date = [NSDate date];
NSTimeInterval secondsSince2001 = [date timeIntervalSinceReferenceDate];
NSTimeInterval secondsSince1970 = [date timeIntervalSince1970];
long int difference = trunc(secondsSince2001 - secondsSince1970);

    You could then add "difference" to your integer values before using it in your select statement.