6

I'm working on improving my iMessages backup-script. /Users/Kyro/Library/Messages/chat.db has a table message with all the messages an some dates.

Any idea what's the date format ? I first thought of a timestamp (I have for example 408978598) but date -r 408978598 return a date in 1982.

4 Answers 4

10

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") 
6
  • 2
    Yes, it is now a nanosecond field. Oct 13, 2017 at 2:06
  • 2
    As of 2018, this answer works as expected. Jan 10, 2018 at 22:43
  • 2
    As of 2019, this answer works as expected
    – user44764
    Jan 20, 2019 at 2:05
  • 1
    As of 2020, this answer works as expected. Jul 14, 2020 at 20:50
  • 4
    As of 2021, this answer works as expected. 🎉
    – Chris
    Feb 3, 2021 at 0:04
3

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;
1
  • This doesn't work for me, all dates have the year -1400
    – Andy Ray
    Jan 8, 2018 at 4:21
1

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.

1
  • To see it in your default local timezone, omit the -u option and add the UTC timezone indicator to the date string: $ gdate -d "2001-01-01Z + 408978598 seconds"
    – pimlottc
    Sep 15, 2015 at 7:36
0

In the script you are referring to, you can achieve timestamping like so: datetime(date + strftime('%s', '2001-01-01 00:00:00'), 'unixepoch', 'localtime') as date from message--to get in in local time if that's what you are looking for.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .