Group values by year, month, day

[thinkpractice]

Hi there,

I'm trying to migrate my app to SharingGRDB/StructuredQueries. So far so good. What I'm trying to achieve is to group values by part of a timestamp. I have this table in my database:

@Table
struct Weight: Identifiable, Equatable, CustomDebugStringConvertible, Hashable, Codable {
    let id: Int
    var timestamp: Date
    var weightInKilograms: Double

    init(id: Int, timestamp: Date, weight: Measurement<UnitMass>) {
        self.id = id
        self.timestamp = timestamp
        self.weight = weight
    }
    ....
}

as you can see the timestamp contains a date along with a timestamp. Now what I would like to do is to create an overview of all weights per day:

import Foundation
import SharingGRDB
import SwiftUI

@Selection
struct GroupedWeights: Equatable, Hashable {
    var date: Date

    @Column(as: [Weight].JSONRepresentation.self)
    var weights: [Weight]
}

@Observable
class WeightListModel {
    @ObservationIgnored
    @Dependency(\.defaultDatabase) var database

    @ObservationIgnored
    @FetchAll
    var groupedWeights: [GroupedWeights]

    var startDate: Date
    var endDate: Date

    init(startDate: Date, endDate: Date) {
        self.startDate = startDate
        self.endDate = endDate
        self._groupedWeights = FetchAll(
            Weight
                .where { $0.timestamp.between(#bind(startDate), and: #bind(endDate)) }
                .group { $0.timestamp }
                .order { $0.timestamp.desc() }
                .select { GroupedWeights.Columns(date: $0.timestamp, weights: $0.jsonGroupArray()) }
        )
    }

}

the code above works, but the .group { $0.timestamp } takes the full time into account. I would like to model something like this now:

select * from weight
group by date(timestamp)
order by timestamp

and later I will need:

select * from weight
group by strftime('%Y%m', timestamp)
order by timestamp

but I'm struggling to implement any of the above. Is it possible to implement anything like this with StructuredQueries?

kind regards,

Tim

[stephencelis]

@thinkpractice StructuredQueries comes with a lot of helpers out of the box, but it doesn't currently implement the date functions (we are open to PRs here, but SQLite date functions are in particular quite nuanced/flexible with their input/output). Instead you can use the #sql macro to write any SQL you want in a schema-safe way:

.group { #sql("date(\($0.timestamp))" }
// or
.group { #sql("strftime('%Y%m', \($0.timestamp))") }

The #sql macro and safe SQL strings are covered in the README and documentation. Let us know if we can better surface this information and if there's an opportunity to have made it easier for you to discover!

[thinkpractice]

Hi @stephencelis,

Yes I've tried that syntax too, but then I'm getting this compiler error:

Generic parameter `QueryValue` could not be inferred

What am I doing wrong here? As for making the api easier, maybe some overload for these functions? i.e.

.group { $0.timestamp.date() }

.group { $0.timestamp.strftime('%Y%m')

or maybe something equivalent to .datecomponents([.year, .month])? The advantage of the latter would be it would be SQL dialect independent as the above would look like this:

group by date_part('year', timestamp), date_part('month', timestamp)

in Postgres for example. Something like this would then be more readable I think:

.group { ($0.timestamp.year, $0.timestamp.month) }

[stephencelis]

@thinkpractice Ah, that's true, currently you must specify the type if it can't be inferred, and at the moment it can't here. So you'd need to do:

.group { #sql("date(\($0.timestamp))", as: Date?.self) }

We can look into if it's possible to weaken this requirement, though!

As for making the api easier, maybe some overload for these functions?

If you are able to define them in reasonable ways we'd be happy to consider them for inclusion in the library, but they are complicated in how they accept various inputs (dates, text, integers, doubles) and how they can fail.

[stephencelis]

@thinkpractice I've also submitted this PR if you want to take the branch for a spin: #79

[thinkpractice]

@stephencelis the .group { #sql("date(\($0.timestamp))", as: Date?.self) } did the trick thanks!

I noticed that the same doesn't work in the select clause though:

.select {
                    GroupedWeights.Columns(date: #sql("date(\($0.timestamp))", as: Date?.self) ?? $0.timestamp, weights: $0.jsonGroupArray())
                }

This fails (I think) because of the conversion back to Date in the @Selection method above. I guess than you need to provide the ISO format and strftime?

[thinkpractice]

@stephencelis the .group { #sql("date(\($0.timestamp))", as: Date?.self) } did the trick, thanks!

I found out that the same code would not work in the select clause though:

.select {
                    GroupedWeights.Columns(date: #sql("date(\($0.timestamp))" ?? $0.timestamp, weights: $0.jsonGroupArray())
 }

It throws an error when mapping back to the date in the @Selection struct. I guess than you need to use the standard ISO format for dates? I.e. use the strftime above?

[thinkpractice]

@stephencelis the .group { #sql("date(\($0.timestamp))", as: Date?.self) } did the trick! thanks!