Created
January 25, 2021 17:40
-
-
Save sweenzor/fded77f306aa3f612cfc6931fc12a8f6 to your computer and use it in GitHub Desktop.
Extract created at and modified at dates from Apple Notes
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment