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.
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
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.
(I wasn't sure where to post this, but since it seems like it might be Apple specific, this seemed like the best spot.)