what is the format of date stored in SQLite through SwiftData?

Question

I am using SwiftData for persistence. Suppose I have a class:

@Model
class Book {
   var id: UUID
   var name: String
   var createdAt: Date

   .... // init
}

When I store a object in SQLite through SwiftData, I got the ZCREATEAT date column:

ZCREATEDAT 
----------------
730049036.810926

What is this? If I want to modify the value in SQLite for debugging purposes. How can I transfer the date such as 8/17/2023 to this kind of format?

Answer 1

Dates are stored as timeIntervalSinceReferenceDate (the number of seconds since 1/1/2001). SQLite does not have a function that does that, but it does have unixepoch function, which is equivalent to the Foundation timeIntervalSince1970 (the number of seconds since 1/1/1970).

If you want to UPDATE this column from your SQL tool, you can subtract the unixepoch of 1/1/2001, e.g., set it to unixepoch('2023-08-17') - unixepoch('2001-01-01'). Or you might want to add time offset, as these are stored in UTC/GMT and if you wanted 17 August 2023 as understood in your timezone, you might want to adjust the time accordingly. E.g., for me in UTC-7, to update the timestamp of Item, it was:

update ZITEM
   set ZTIMESTAMP = unixepoch('2023-08-17T07:00:00') - unixepoch('2001-01-01')
   where Z_PK = 1;

Be forewarned that it is easy to screw up CoreData/SwiftData databases when manipulating them using third party tools, so I would encourage that you do your database manipulation from Swift, not SQLite tools.

---

To display dates in SQLite in user-understandable format, add the unixepoch of 1/1/2001. E.g., to display the timeIntervalSinceReferenceDate date stored in the ZTIMESTAMP column of ZITEM table in a new column, DATE:

select *, datetime(ZTIMESTAMP + unixepoch('2001-01-01'), 'unixepoch', 'localtime') AS `DATE`
    from ZITEM;

If you would rather see these strings in UTC/GMT, just omit the 'localtime' parameter.