IOS – What format does the apple notes sqlite db date use

iosiphonenotes.appsqlite

I want to extract the note texts and modification dates from an sqlite notes database extracted from an iOS device backup. But I can't figure out how to convert the date format to a readable date string.

How I got the data

I found out which file had the note data from this page.

Relevant portion:

Step 1. Find the Backup File in ~/Library/Application Support/MobileSync/Backup/fea….627
        something like : ca3b….39c
       If you have trouble seeing Library folder 
       defaults write com.apple.Finder AppleShowAllFiles Yes
Step 2. Copy file and rename as notes.sqlite

What I've tried so far

I've opened the file up and confirmed it has the expected note body text inside it. The body text is inside a table called ZNOTEBODY. Another table called ZNOTE has a a ZBODYcolumn which appears to contain indices into the ZNOTEBODY table, and a ZMODIFICATIONDATE column. Here is the crux of the matter: The ZMODIFICATIONDATE column has a listed type of TIMESTAMP and it contains floating point numbers.

I looked at the sqlite docs and found this:

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

So I tried using the built-in datetime function with a simple SELECT datetime(ZMODIFICATIONDATE) FROM ZNOTE; but that produced nonsense values like 1171380-13689427-18 21:08:40 for an entry I know corresponds to 2014-08-12. The stored value in question was 429559087.38102.

So, does anyone know what format is being used here? Or, more importantly how to convert it to a readable date?

The following stored dates all correspond to (different points on) 2014-08-11, in order of earliest to latest.

  • 429435093.400244
  • 429446655.298724
  • 429447111.140259
  • 429460783.273032
  • 429464301.184442

(I wasn't sure where to post this, but since it seems like it might be Apple specific, this seemed like the best spot.)

Best Answer

According to this answer on Stack Overflow, it's a Unix timestamp adjusted by 31 years. This is typical of CoreData, Apple's ORM framework. The following would work:

select datetime(zmodificationdate,'unixepoch','31 years') from znote
Related Question