Apple - Dates format in Message's chat.db
I found that as of High Sierra, I needed to use a different date transformation. The 8 extra 0's on the date field versus the date_read field was the clue:
datetime(message.date/1000000000 + strftime("%s", "2001-01-01") ,"unixepoch","localtime")
To add on to @Matthieu's helpful answer, here's how to convert it within SQLLite:
datetime(date + strftime('%s','2001-01-01'), 'unixepoch')
For example:
select
is_from_me,
datetime(date + strftime('%s','2001-01-01'), 'unixepoch') as date_utc,
text
from message;