Importing notes from Bear

Bear, my note-taking app of choice, stores its notes in a SQLite database at ~/Library/Group Containers/9K33E3U3T4.net.shinyfrog.bear/Application Data/database.sqlite. I use the following query to get the content out:

SELECT
  *,
  datetime(zmodificationdate, 'unixepoch', '31 years')
    AS zmodificationdate2
FROM
  zsfnote
WHERE
  ztrashed == 0 AND zarchived == 0

The zmodificationdate2 derived column takes care of the way timestamps are stored in Bear: these are Core Data timestamps, which are the number of seconds elapsed since January 1st, 2001.∗It’s unclear to me why they wouldn’t use regular Unix time. 

Once I have the query, I can load the notes from the database:

notes = db[query].map do |row|
  {
    id: row[:ZUNIQUEIDENTIFIER],
    title: row[:ZTITLE],
    text: row[:ZTEXT],
    modification_date: row[:zmodificationdate2]
  }
end

I don’t use the note ID directly. The IDs of Bear notes are quite long (longer than UUIDs). I hash the UUIDs, then Avoid naughty words in hex digest strings by changing the set of characters, and finally take the first 15 characters, dash-separated in groups of 5 characters, as the new ID, e.g. 7fmkz-pkwxt-kcxvt, which I use in note URLs.