DateComponents best practises?

[mtancock]

What did you do?

Good question...

I'm storing information which includes dates, however the dates don't need to be precise - I'm only interested in YMD. I read in the readme that GRDB supports reading/writing to the db as dateComponents, but having a hard time visualising how I should set this up.

I'm currently creating my records as structs e.g

struct Activity: Codable, FetchableRecord, PersistableRecord {
    var id: String?
    var name: String
    var date: <What goes here?>
}

My migration looks like this:

try database.create(table: "activity") { table in
    table.column("id", .text).notNull().primaryKey()
    table.column("name", .text).notNull()
    table.column("date", .date).notNull()
}

I've tried using date directly, and at one point I had three separate fields for YMD as Ints, and then recreated them after fetching from the database but that seems annoying. Then I read the Readme (should have started there...) and see that there's support for DateComponents via DatabaseDateComponents but in terms of defining my record, saving back to the db etc. , I'm not sure how I should be using it. Any tips or suggestions where I should be looking to figure this out?

Thanks for all your work on this library. It's great.

Environment

GRDB flavor(s): Regular GRDB
GRDB version: 4.13
Installation method: Cocoapods
Xcode version: 11.3.1
Swift version: 5ish
Platform(s) running GRDB: iOS/macOS
macOS version running Xcode: 10.15.4

[groue]

Hello and thank you, @mtancock

Let's start from your app:

I'm storing information which includes dates, however the dates don't need to be precise - I'm only interested in YMD

It's indeed frequent to only care about YMD. Not all date-related contexts such as calendar (gregorian, japanese, locale-dependent, etc.) or time zones (local, utc, etc.) are always relevant. For example, YMD is a perfect storage format for birth dates (although it may be tactful to make the year optional).

Your first question should be about the type of those dates, the type that you want your application to deal with. It's too early to think about the database. Just think about the domain you are modelling, and the constraint you want your type to express.

• Foundation.Date is an absolute point in time, and does not look like a great fit.
• Foundation.DateComponents is better, but it forces your app to deal with its optional year, month, and day properties. Is it a big deal? I don't know, it all depends on your application constraints.
• A custom YMD struct with non-optional year, month, and day properties is cool, but you'll spend time converting it back and forth from Foundation.DateComponents each time you want to perform calendar calculations on it.

Make up your mind, and now we can dig onto the database level.

Whatever your choice of a type is, we have a "problem" to solve: there's no direct and built-in database support for Foundation.DateComponents and YourApp.YMD, the types we want to expose to the application.

We have a situation where the database does not talk the same language as the rest of the application.

Well, this is not exceptional at all, and a record type is the perfect place to solve this divergence.

A "classic" example of such situation is the following: the database speaks "latitude" and "longitude", but the rest of the application speaks "coordinate":

struct Place: Codable, FetchableRecord, PersistableRecord {
    var id: String
    private var latitude: CLLocationDegrees
    private var longitude: CLLocationDegrees
    
    var coordinate: CLLocationCoordinate2D {
        get {
            CLLocationCoordinate2D(latitude: latitude, longitude: longitude)
        }
        set {
            latitude = newValue.latitude
            longitude = newValue.longitude
        }
    }
}

How does it apply to Foundation.DateComponents and YourApp.YMD? In exactly the same fashion: the record type has private properties that talk to the database, and public/internal properties that talk to the rest of the application:

struct Activity: Codable, FetchableRecord, PersistableRecord {
    var id: String
    var name: String
    private var date: DatabaseDateComponents
    
    var dateComponents: DateComponents {
        get {
            date.dateComponents
        }
        set {
            date = DatabaseDateComponents(newValue, format: .YMD)
        }
    }
}

The strategy is always the same:

1. Pick the type you want your app to deal with.
2. If this type has no built-in database support, find another type with built-in database support which can be converted to and from the first type.
3. Expose the first in public/internal properties, hide the second for private record use only.

In some very rare occasions, you may want to extend a type with database support. This is discussed in the Custom Value Types. But I'd strongly recommend against - the strategy described above is much more simple and clear.

[groue]

Swift version: 5ish

;-)

[mtancock]

Ah thanks @groue . That's an amazing answer - and you're right. I was too focussed on thinking about the database format.

Quick follow up question: It seems like DatabaseDateComponents doesn't conform to Codable, so I'll lose automatic Codable synthesis. Is that accurate?

[groue]

Is that accurate?

Oops, a missing piece!

What do you prefer: submitting a pull request, or wait? 😉

[mtancock]

I'll take a look, but no promises! 😀

[groue]

No problem, of course. You've found a missing piece, so a fix will ship. I just can't provide any ETA, and it may have to wait until GRDB 5. This can be solved today, in your app, by adding Codable conformance yourself. You'll be very close from a pull request, then.