Date Format in sqlite message table (iphone7)

What is this number supposed to be ?

I tested as if it was elapsed since 1970 (reference on Mac) in micro or nano seconds, but that does not give the expected date.

extension Date {
    var millisecondsSince1970: Int64 {
        Int64((self.timeIntervalSince1970 * 1000.0).rounded())
    }
    
    init(milliseconds: Int64) {
        self = Date(timeIntervalSince1970: TimeInterval(milliseconds) / 1000)
    }

    init(microseconds: Int64) {
        self = Date(timeIntervalSince1970: TimeInterval(microseconds) / 1000_000)
    }

    init(nanoseconds: Int64) {
        self = Date(timeIntervalSince1970: TimeInterval(nanoseconds) / 1000_000_000)
    }
}

print("micro", Date(microseconds: 565_599_639_274_028_992)) // micro 19893-02-20 02:54:34 +0000
print("nano",  Date(nanoseconds : 565_599_639_274_028_992)) // nano 1987-12-04 07:00:39 +0000

Is the number a time since about 2000, expressed as nanoseconds ?

I tested this:

let aDateStr = "04/12/2018"

// Create Date Formatter
let aDateFormatter = DateFormatter()
aDateFormatter.dateFormat = "dd/MM/yy"

// Convert String to Date
let aDate = aDateFormatter.date(from: aDateStr)!
let elapsed : Double = 565_599_639_274_028_992 / 1000_000_000

let newDate = Date(timeInterval: -elapsed, since: aDate)
print("newdate", newDate)

and get:

newdate 2000-12-31 15:59:20 +0000

So, the Int is probably a time elapsed in micro seconds since 1/1/2001.

Then, to convert to date, just do the other way:

let refDateStr = "01/01/2001"

// Create Date Formatter
let aDateFormatter = DateFormatter()
aDateFormatter.dateFormat = "dd/MM/yy"
aDateFormatter.timeZone = TimeZone(abbreviation: "GMT")

// Convert String to Date
let refDate = aDateFormatter.date(from: refDateStr)!
let elapsed : Double = 565_599_639_274_028_992 / 1000_000_000

let newDate = Date(timeInterval: elapsed, since: refDate)
print("newDate", newDate)

And get:

newDate 2018-12-04 07:00:39 +0000

Which is the expected result, at 7 am, GMT.

Of course, need to set timezone appropriately.

I am not sure what You mean... I don't know anything more about...

That simple message flow was like that:

4/dec/2018 about 7-8 a.m. GMT+1...

Iphone to other: Message sent at 565597739831296448

Other to Iphone Message received at: 565599639274028992 That message has been received by phone 4/dec/2018 about 7-8 a.m. GMT+1... next column in table is: date_read and it has value: 565604380

That values looks same in text format

When you use data, you'd better know what is their definition.

However, I do think my guess is correct, it is the time elapsed in nanoseconds since 1/1/2001.

Hence the code I sent should answer your question:

let refDateStr = "01/01/2001"

// Create Date Formatter
let aDateFormatter = DateFormatter()
aDateFormatter.dateFormat = "dd/MM/yy"
aDateFormatter.timeZone = TimeZone(abbreviation: "GMT")

// Convert String to Date
let refDate = aDateFormatter.date(from: refDateStr)!
let elapsed : Double = 565_599_639_274_028_992 / 1000_000_000

let newDate = Date(timeInterval: elapsed, since: refDate)
print("newDate", newDate)

The second value is an elapsed 1 hour later, expressed in seconds, not nanoseconds.

Then just compute date as:

let elapsed2: Double = 565_604_380  // in seconds
let newDate2 = Date(timeInterval: elapsed2, since: refDate)
print("newDate2", newDate2)

And get:

newDate2 2018-12-04 08:19:40 +0000