14

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.

David Young
  • 441
  • 1
  • 4
  • 13
  • No, it's not jailbroken, nor was it at the time of the messages. I'm getting the info from the 3d0d... file in the iPhone backup directory. – David Young May 25 '12 at 05:59
  • Hi David, I am looking to convert my sms backups into json or csv or anything useable. Did you happen to complete a script for something to this effect? – Quddus George Jul 24 '20 at 00:35

8 Answers8

14

Since the backup is exported to SQLite database format, here's how to convert the number to a real date in SQLite:

select
    datetime(date + strftime('%s', '2001-01-01 00:00:00'),
       'unixepoch', 'localtime') as date,
    *
from message
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 1
    Don't forget to include SUBSTR(date, 1, 9) here... like so `select datetime(SUBSTR(date, 1, 9) + strftime('%s', '2001-01-01 00:00:00'), 'unixepoch', 'localtime') as date, * from message` – PaulMest Jan 21 '19 at 00:45
8

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
Ryan DuVal
  • 3,774
  • 2
  • 21
  • 15
  • 1
    i'd been trying other/previous dateTime conversions without success. thanks @ryan-duval! – rikb Oct 11 '19 at 20:29
7

It is in seconds since 1/1/2001 instead of the others which are Unix based off of 1/1/1970. So to convert it to say an Excel time your formula would be =Cell/(60*60*24) + "1/1/2001".

AManning
  • 71
  • 1
2

Apple uses Mac Absolute Time (MacTime). This is counted from 01-01-2001. The other timestamp you see is UnixTime. This starts from 01-01-1970.

You have to add 31 years to MacTime to get UnixTime. This is a PHP-snippit:

$macTime = $d['ZMESSAGEDATE']; // MacTime column (from Whatsapp)
$unixTime =  $macTime + 978307200;
echo date("Y-m-d H:i:s", $unixTime);

The time difference is calculated using this website: https://www.timeanddate.com/date/durationresult.html?d1=1&m1=1&y1=1970&d2=1&m2=1&y2=2001&h1=0&i1=0&s1=0&h2=0&i2=0&s2=0

joram
  • 143
  • 7
0

There may be another answer.

=Cell/(60*60*24) + "1/1/1970"

works with my current version of the iPhone/iOS => 4.3.3

Pragnesh Chauhan
  • 8,363
  • 9
  • 42
  • 53
Jerome
  • 5,583
  • 3
  • 33
  • 76
0

Fomurla with time of messages: =Cell/(60*60*24) + "1/1/2001 7:00"

Jack Bui
  • 160
  • 1
  • 2
  • 9
0

Since date in mac is calculated from 2001 and not 1970, we have to add some extra to this mac date.

978307200000 is equivalent to milliseconds until 2001-01-01

Also multiplying by 1000 is required to convert to milli-seconds.

macDate * 1000 + 978307200000
Harry
  • 1,572
  • 2
  • 17
  • 31
-1

Bohemian♦ is right, but there's a little typo in his answer:

use %S (capitals) instead of %s, since the time is represented in seconds since 2001 and not 1970!

Doc from https://www.sqlite.org/lang_datefunc.html

%s      seconds since 1970-01-01
%S      seconds: 00-59

select datetime(date + strftime('%S', '2001-01-01 00:00:00'), 'unixepoch', 'localtime') as date, * from message

xephon
  • 7
  • 1
  • 1
    just tried you example in SQLite query and it gives incorrect results with "%S", while "%s" gives correct results. – Andrey Mar 12 '16 at 19:23