Apple Core Data timestamp converter

Timestamps in Apple's message databases do not count from 1970. They count from 1 January 2001, 00:00:00 UTC, an epoch variously called Mac absolute time, Core Data time or Cocoa time. The offset between the two is exactly 978307200 seconds.

Read one of these numbers as a unix timestamp and you get a date that is wrong by just over thirty one years, in a way nothing on screen will flag: it is still a plausible date, still in the right order relative to the other rows. That is the whole reason this page exists.

Convert a value

Paste the number from the date, date_read or date_delivered column. Whether it is in seconds or nanoseconds is worked out below, and everything happens in this browser: nothing is sent anywhere.

Go the other way

A date in, the two values a database would hold out. Useful when you are writing a WHERE clause over a date range rather than reading a single row.

Seconds or nanoseconds, and how to tell

The unit changed with iOS 11. Older backups store whole seconds since the Apple epoch; iOS 11 and later store nanoseconds in the same column, with no flag anywhere saying which you are looking at. You do not need the iOS version to decide, because the two ranges do not come close to overlapping:

• A value below about 10¹² is seconds. In nanoseconds that would be 1970-something, decades before the epoch the column counts from.
• A value above it is nanoseconds. As seconds it would be somewhere past the year 33,000.

A 0 is neither. It is a row that never received a real timestamp, which happens with drafts and with rows a database repair has been through, and it converts to the epoch itself rather than to a date anybody should quote.

The same conversion in SQL

For a whole table rather than one row, SQLite does it in one expression:

-- iOS 11 and later (nanoseconds)
SELECT datetime(date/1000000000 + 978307200, 'unixepoch') FROM message;

-- iOS 10 and earlier (seconds)
SELECT datetime(date + 978307200, 'unixepoch') FROM message;

Both give UTC. Add 'localtime' as a third argument and SQLite converts to the timezone of the machine running the query, which is almost never the timezone you want. See below.

In PowerShell, for a single value:

[DateTimeOffset]::FromUnixTimeSeconds(978307200 + 782264400).UtcDateTime

What the number does not carry

The column is UTC, and it does not record where the phone was. Nothing in the timestamp says which timezone the handset was set to when the message arrived, so "the local time" printed by any tool, this one included, is the local time of the computer doing the reading. Convert on a machine in Warsaw and a message sent in Chicago acquires a Warsaw clock time.

That matters when two sides of a case produce the same conversation from different machines and the two printouts disagree about when a message was sent. They are both converting the same UTC instant and neither is lying. Quote the UTC value, say which timezone any local time was rendered in, and the argument goes away.

Where these numbers live

On an iPhone backup, in Library/SMS/sms.db under the HomeDomain domain, where the file is stored under a hashed name rather than its own. There is a calculator for that name too. On macOS the same schema is ~/Library/Messages/chat.db.

The schema itself, which columns exist in which iOS generation and why the text column is empty on newer messages, is documented in our public knowledge repository: github.com/ChatExport/ChatExportKnowledge. It is CC BY 4.0 and every claim in it was read off a real backup or is marked as inferred.

If what you actually want is the conversation rather than the table, ChatExport reads that database on a Windows PC and writes a PDF with the timestamps already converted, each export carrying a SHA-256 for the recipient to check independently.