Extract created at and modified at dates from Apple Notes

apple-notes-metadata.py

import time
import datetime
import sqlite3
import json

# Based on https://github.com/ydkhatri/mac_apt/blob/master/plugins/notes.py
query = """
    SELECT
        n.ZNOTE as id,
        c1.ZTITLE1 as title,
        c1.ZIDENTIFIER as uuid, 
        c1.ZCREATIONDATE1 as created,
        c1.ZMODIFICATIONDATE1 as modified,
        c2.ZTITLE2 as folder
    FROM ZICNOTEDATA as n 
    LEFT JOIN ZICCLOUDSYNCINGOBJECT as c1 ON c1.ZNOTEDATA = n.Z_PK 
    LEFT JOIN ZICCLOUDSYNCINGOBJECT as c2 ON c2.Z_PK = c1.ZFOLDER 
    LEFT JOIN ZICCLOUDSYNCINGOBJECT as c3 ON c3.ZNOTE= n.ZNOTE
    ORDER BY id
"""

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

def fix_dates(row):
    coredata_epoch = time.mktime(
        datetime.datetime(2001, 1, 1, 0, 0, 0, 0, tzinfo=None).timetuple()
    )
    if row['created']:
        row['created'] = datetime.datetime.utcfromtimestamp(
            row['created'] + coredata_epoch).isoformat()
    if row['modified']:
        row['modified'] = datetime.datetime.utcfromtimestamp(
            row['modified'] + coredata_epoch).isoformat()

conn = sqlite3.connect('NoteStore.sqlite')
conn.row_factory = dict_factory
c = conn.cursor()
c.execute(query)
notes_data = c.fetchall()
conn.close

for note in notes_data:
    fix_dates(note)

with open('metadata.json', 'w') as outfile:
    json.dump(notes_data, outfile)