Comparing dates in the where clause

[pfandrade]

I have a column that representes a Date stored as an ISO8601 string

@Column(as: Date.ISO8601Representation.self)
var dueAt: Date

I would like to be able perform a query like so:

.where { $0.dueAt.lt(now) } // now is of type Date

This isn't possible because Date doesn't conform to QueryExpression.

I could change it to

.where { $0.dueAt.lt(Date.ISO8601Representation(queryOutput: now)) }

But this doesn't produce the right SQL because now is not surrounded in a DATETIME call.

If I now surround that with a call to the global dateTime() function:

.where { $0.dueAt.lt(dateTime(Date.ISO8601Representation(queryOutput: now))) }

I get two different errors:
Global function 'dateTime' requires that 'Date.ISO8601Representation' conform to 'SQLSpecificExpressible'
Instance method 'lt' requires that 'SQLExpression' conform to 'QueryExpression'

I can just use #sql() and go about my day, but if feels like comparing dates should be easy to do using structured queries.

Am I missing something?

[mbrandonw]

Hi @pfandrade, ISO8601Representation is the default representation for dates in StructuredQueries, and so you should be able to just do this:

var dueAt: Date

…

.where { $0.dueAt.lt(now) }

Further, the dateTime that you are reaching for actually belongs to GRDB and not this library, and so it is not compatible to use. If you want access to the SQL function for the current date you will want to use the #sql macro:

.where { $0.dueAt.lt(#sql("datetime()")) }

Another alternative is to define a Swift function for the current date (it can even use @Dependency(\.date.now) under the hood so that it is controllable), install that function in your DB connection, and then use it from SQL:

@DatabaseFunction
func datetime() -> Date {
  @Dependency(\.date.now) var now
  return now
}

…

// Install the function (typically done when creating the database):
db.add(function: $datetime)

…

.where { $0.dueAt.lt(#sql($datetime())) }

And finally, whenever you do need to use custom representations for your columns, which you don't with Date but you may need to for other data types, then the way you bind data to SQL queries is using the #bind macro:

@Column(as: Date.UnixTimeRepresentation.self)
var dueAt: Date

…

.where { $0.dueAt.lt(#bind(now.timeIntervalSince1970)) }

[pfandrade]

Indeed ISO8601Representation is the default representation. On the SQL statement you'll get something like '2025-09-22 22:24:28.617'. But you'll end up comparing strings, not dates, right? This would work if the strings use exactly the same date format.

But in my case that now is actually from @Dependency(\.date.now) and dueAt is a complex SQL query:

extension WishedItem.TableColumns {
    var dueAt: some QueryExpression<Date.ISO8601Representation> {
        #sql("DATETIME(COALESCE(\(refreshedAt), \(updatedAt), \(createdAt)), '+' || \(trackingInterval) || ' seconds')")
    }
}

The former gets converted to a date with sub seconds, but the latter isn't.

For example, consider the following tests:

sqlite> select 'true' where '2025-09-22 22:24:28' >= '2025-09-22 22:24:28';
true
sqlite> select 'true' where '2025-09-22 22:24:28' >= '2025-09-22 22:24:28.000'; 
sqlite> select 'true' where DATETIME('2025-09-22 22:24:28') >= DATETIME('2025-09-22 22:24:28.000');
true

Note how the second query doesn't output true when the dates are indeed the same. SQL is doing string comparison here.

• I now know I can change the query for dueAt to output its date with sub seconds, which fixes my issue.
  This still feels fragile, but I'm learning SQLite's DATETIME doesn't really convert to a proper date, it's more of a formatter. For example, you can't do arithmetic like this select DATETIME('2025-09-22 22:24:29') - DATETIME('2025-09-22 22:24:28');.

  I guess I just have to remember that when working with dates and raw SQL I need to use sub seconds to match how the framework works.

[mbrandonw]

But you'll end up comparing strings, not dates, right?

Yes, but that's just how SQL dates work. You compare dates as strings.

But in my case that now is actually from @Dependency(\.date.now) and dueAt is a complex SQL query:

extension WishedItem.TableColumns {
    var dueAt: some QueryExpression<Date.ISO8601Representation> {
        #sql("DATETIME(COALESCE(\(refreshedAt), \(updatedAt), \(createdAt)), '+' || \(trackingInterval) || ' seconds')")
    }
}

If you are wanting to perform those kinds of date computations in SQLite then maybe you should use the unix epoch representation instead of ISO8601.

• I now know I can change the query for dueAt to output its date with sub seconds, which fixes my issue.
  This still feels fragile, but I'm learning SQLite's DATETIME doesn't really convert to a proper date, it's more of a formatter. For example, you can't do arithmetic like this select DATETIME('2025-09-22 22:24:29') - DATETIME('2025-09-22 22:24:28');.

It may be fragile, but it's just how dates work in SQLite. Overall the problems you are bringing up aren't really related to this library and instead are just SQLite questions in general.

[mbrandonw]

You should also familiarize yourself with the date functions in SQLite here. The computations you are performing can be done by converting to julian/unix and back to ISO8601.

[pfandrade]

Yes, I wasn't aware SQLite doesn't actually compare Dates per se but strings. The root issue was that my custom SQL using DATETIME was not using sub seconds. Which would cause the comparison to fail. I don't actually need to do use julian/unix dates since I'm not doing any arithmetic.

Having understood all this, I don't think anything should change on the library. Perhaps maybe a comment about dates using sub seconds and the default DATETIME function isn't. But I'm not sure where the best place to put it in the docs.

I'm closing this. Thanks for all your help!

[mbrandonw]

And just in case you don’t already know, the datetime function in sql can be given a modifier argument of ‘subsec’ to compute the date with sub-seconds.

[pfandrade]

Yep, that was my solution.