How to model a query that uses a Date Range?

[thinkpractice]

Hi there,

I'm trying to retrieve a series of @Table objects from the sqlite version within a certain date range. Right now, the code looks like this:

  func getWeightsForDateRange(startDate: Date, endDate: Date) -> [Weight] {
        do
        {
            return try database.read { db in
                return try Weight
                    .order(by: \.timestamp)
                    .where {
                        $0.timestamp.between(startDate, and: endDate)
                    }
                    .fetchAll(db)
            }
        }
        catch {
            Logger.data.error("Error fetching weights: \(error)")
        }
        return []
    }

This does not compile without any changes. The error I'm getting indicates that to make this compile I should conform Date to QueryExpression, so I added the following extension:

extension Date : @retroactive QueryExpression
{
    public typealias QueryValue = Date.ISO8601Representation
    
    public var queryFragment: StructuredQueriesCore.QueryFragment {
        
        return StructuredQueriesCore.QueryFragment(stringLiteral: "'" + self.ISO8601Format() + "'")
    }
}

This makes the code compile, but the resulting sql is not valid :(. It results in the following error:

Error fetching weights: SQLite error 1: near ")": syntax error - while executing `SELECT "weights"."id", "weights"."timestamp", "weights"."weightInKilograms" FROM "weights" WHERE ("weights"."timestamp" BETWEEN ('2025-04-27T22:00:00Z' AND '2025-06-01T22:00:00Z')) ORDER BY "weights"."timestamp"`

Is the above extension the way to go or am I missing something? Shouldn't Date have such conformance added by SharingGRDB in the first place?

kind regards,

Tim

[thinkpractice]

Tried the above query in DBeaver and indeed it's invalid sql, the following queries do work:

SELECT "weights"."id", "weights"."timestamp", "weights"."weightInKilograms" FROM "weights" 
WHERE ("weights"."timestamp" BETWEEN '2025-04-27T22:00:00Z' AND '2025-06-01T22:00:00Z') 
ORDER BY "weights"."timestamp"

and

SELECT "weights"."id", "weights"."timestamp", "weights"."weightInKilograms" FROM "weights" 
WHERE "weights"."timestamp" BETWEEN '2025-04-27T22:00:00Z' AND '2025-06-01T22:00:00Z' 
ORDER BY "weights"."timestamp"

it looks like sqlite has issues with the brackets around BETWEEN, because this doesn't work either:

SELECT "weights"."id", "weights"."timestamp", "weights"."weightInKilograms" FROM "weights" 
WHERE "weights"."timestamp" BETWEEN ('2025-04-27T22:00:00Z' AND '2025-06-01T22:00:00Z') 
ORDER BY "weights"."timestamp";

Is there something I'm doing wrong here or is this some possible issue in StructuredQueries?

kind regards,

Tim

[mbrandonw]

Hi @thinkpractice, because Date can be represented in SQLite in multiple ways (string, double, int), you can't simply bind the value directly to a query. You have to further tell it the representation to use to bind.

If you use the #bind macro though, it can infer this information for you:

import Foundation
@Table struct Widget {
  @Column(as: Date.ISO8601Representation.self)
  var date: Date
}
func query() {
  Widget.where {
    $0.date.between(#bind(Date()), and: #bind(Date()))
  }
}

Also, in pointfreeco/swift-structured-queries#37 we are considering adding a default representation to Date and UUID so that you don't have to do these kinds of dances. That will make things easier, but for now #bind is your best bet.

[thinkpractice]

Hi @mbrandonw,

Thanks! I've tried #bind (also before) but somehow it doesn't work either, here's my code:

 func getWeightsForDateRange(startDate: Date, endDate: Date) -> [Weight] {
        do
        {
            return try database.read { db in
                return try Weight
                    .order(by: \.timestamp)
                    .where {
                        $0.timestamp.between(#bind(startDate), and: #bind(endDate))
                    }
                    .fetchAll(db)
            }
        }
        catch {
            Logger.data.error("Error fetching weights: \(error)")
        }
        return []
    }

Also here I'm getting an error concerning the brackets:

Error fetching weights: SQLite error 1: near ")": syntax error - while executing 
`SELECT "weights"."id", "weights"."timestamp", "weights"."weightInKilograms" FROM "weights" 
WHERE ("weights"."timestamp" BETWEEN (? AND ?)) 
ORDER BY "weights"."timestamp"`

I guess the positional placeholders are correct? See that again there are nested brackets in the where clause? I tried this using dbeaver and this doesn't work (see above). Am I overseeing something?

[mbrandonw]

Ah, that just seems like a bug in our generated SQL! We should not be parenthesizing the ? AND ? when using BETWEEN. We will fix that soon.

[thinkpractice]

@mbrandonw that would be great! Thanks! Should I file an issue? Or is this discussion enough?

[mbrandonw]

Got a fix over here: pointfreeco/swift-structured-queries#47

Will merge and release sometime soon.

[thinkpractice]

Thanks :) Will keep an eye out for when it arrives.

[thinkpractice]

@mbrandonw saw that the fix is already part of the new release. Thanks!

[thinkpractice]

@mbrandonw I've fixed my code to use the #bind command and this results in the correct query:

func getWeightsForDateRange(startDate: Date, endDate: Date) -> [Weight] {
        do
        {
            return try database.read { db in
                let weights = try Weight
                    .order(by: \.timestamp)
                    .where {
                        $0.timestamp.between(#bind(startDate), and: #bind(endDate))
                    }
                    .fetchAll(db)
                return weights  
            }
        }
        catch {
            Logger.data.error("Error fetching weights: \(error)")
        }
        return []
    }

I'm using this query to return the weighs for a month. This works correctly in the unit tests. The query generated by the UI for June is:

SELECT "weights"."id", "weights"."timestamp", "weights"."weightInKilograms" FROM "weights" 
WHERE ("weights"."timestamp" BETWEEN '2025-04-27 22:00:00.000' AND '2025-06-01 22:00:00.000') 
ORDER BY "weights"."timestamp"

When I try this in DBeaver it return the correct number of five records for May. However, SharingGRDB in the UI/App only returns the last record I added today. I have been trying to find the cause of the behavior. Is there any difference between the UI and tests apart from the UI using a real database? Do you have an idea what could be going wrong?

EDIT: If I add another record today, it also shows up correctly. However, when I change the date of a previous record to today (using DBeaver) it does not show up for today.

Also I'm displaying all weights in a chart using:

 @FetchAll(Weight.order(by: \.timestamp)) private var weights: [Weight]

and this does return all the weights in the database.