Dates format in Message's chat.db

Solution 1:

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") 

Solution 2:

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;

Solution 3:

well using coreUtils date :

I found what I was looking for :

$gdate -ud "2001-01-01 + 408978598 seconds" 
Tue Dec 17 13:09:58 UTC 2013

It's a timestamp with base date January 1st, 2001.