Parsing "date" field of iPhone SMS file from backup

Question

While this isn't a programming question per se, it IS related.

So I'm trying to figure out how to parse the SMS DB that gets backed up from the iPhone. I'm looking at the "messages" table, specifically the "date" field. I noticed that the more recent messages are using a different numbering system to indicate the date/time. I've narrowed it down to the switch to iMessage, as I have a message sent at 1318470904, with a reply sent at 340164736. I know for a fact that these messages were sent less than an hour apart, yet they're indicating > 30 years' difference.

Anybody know how to accurately calculate the date using this newer system? Is it using a different epoch or is there some crazy math I need to do?

Edit: Recent messages are affected as well. Texts (green bubbles) are stored with the date set normally, and anything through iMessage (blue bubbles) is stored with the different date representation.

Answer 1

I don't know about getting the correct date given two versions present, but when I did this today, I noticed the date column was not the standard unix time but a longer number with seemingly nine zeros at the end, like 444548608000000000. This is what I did to get the correct date:

select
    datetime(substr(date, 1, 9) + 978307200, 'unixepoch', 'localtime') as f_date,
    text
from message