Storing partial date/time values

[pietrasm]

I am exploring options for storing a date without a time component. Using Date normalized to midnight UTC works but introduces imprecise domain modeling and makes it easy to introduce timezone-related bugs. Normalizing to noon UTC mitigates this to some extent, but it still doesn't seem like the right solution.

GRDB has DatabaseDateComponents with a .YMD format, but it seems like it cannot be easily used with swift-structured-queries.

A custom solution could be easily implemented using a struct which stores the data in YYYY-MM-DD format as TEXT by conforming it to LosslessStringConvertible.

Also, I am looking into storing a day of the week which could be easily stored using an enum backed by Int or an OptionSet for a set of weekdays.

Is there a recommended pattern for handling data like this? It seems like these requirements are common enough that they could warrant native support in the library.

[mbrandonw]

Hi @pietrasm, while we do not provide a first class tool for this specific use case, we do provide the tools necessary for you to store any kind of custom data type in the database. You can define a custom representation that conforms to QueryDecodable and QueryBindable in order to describe how to shuffle the data back and forth to SQLite. We have some examples of this here. With that done you would be able to do things like:

struct Reminder {
  @Column(as: Date.DayRepresentation.self)
  var due: Date
}

[pietrasm]

Hi @mbrandonw, thanks for your reply. I am trying to avoid using Date since it represents an exact point in time which includes time of day, so the domain wouldn’t be modeled precisely. DateComponents is better but having all properties optional is not great either.

That led me to define a custom type that holds exactly what I need and can be turned into Date by combining it with a time of day and a timezone:

struct CalendarDate: Hashable, Sendable {
  var year: Int
  var month: Int
  var day: Int
}

extension CalendarDate: LosslessStringConvertible {
  var description: String {
    String(format: "%04d-%02d-%02d", year, month, day)
  }

  init?(_ description: String) {
    let parts = description.split(separator: "-")
    guard parts.count == 3,
      let year = Int(parts[0]),
      let month = Int(parts[1]),
      let day = Int(parts[2])
    else { return nil }
    self.init(year: year, month: month, day: day)
  }
}

extension CalendarDate: QueryBindable {}
extension CalendarDate: QueryDecodable {}

It seems like the easiest way to make a type stored as TEXT conform to QueryBindable and QueryDecodable is via LosslessStringConvertible. Are there any downsides of this approach?