Using a row's value within an expression/ordering (CASE/WHEN/THEN/ELSE/END expression)

[dawilliams-gpsw]

Hello, all!

I am persisting records that contain both a UTC datetime and a timezone offset in my database table to be able to communicate in UTC while displaying local times to the user. The timezone offset can be null or have a value represented in minutes. When the timezone is null, I would like to use dateTime(Column("date"), .localTime) as my ordering method, but if the timezone is non-null, I would like to use that value within the dateTime: dateTime(Column("date"), .minutes(nonNullTimeZoneOffsetHere)).

I have figured out how to do this using raw SQL, using something like the following:

CASE
    WHEN timeZoneOffset IS NULL THEN datetime(\(columnName), 'localtime')
    ELSE datetime(\(columnName),  printf('%d minutes', timeZoneOffset))
END as localizedDate
ORDER BY localizedDate

Is it possible to do something like this using the query interface? If not, how can I embed this in a query interface request with other selections/etc? Would Common Table Expressions help me here? How about .annotated(...)?

Thanks in advance :)

[groue]

Hello @dawilliams-gpsw,

There is no built-in support for the general CASE WHEN THEN ELSE END expression in GRDB, but you can build your specific one:

func localizedDate(
    timeZoneOffset: some SQLExpressible,
    date: some SQLExpressible)
-> SQLExpression
{
    SQL("""
        CASE WHEN \(timeZoneOffset) IS NULL
        THEN datetime(\(date), 'localtime')
        ELSE datetime(\(date), printf('%d minutes', \(timeZoneOffset)))
        END
        """).sqlExpression
}

Usage:

// SELECT *, CASE...END AS localizedDate
// FROM ...
// ORDER BY localizedDate
let localizedDate = localizedDate(
    timeZoneOffset: Column("timeZoneOffset"), 
    date: Column("date"))

let request = try MyRecord
    .annotated(with: localizedDate.forKey("localizedDate"))
    .order(Column("localizedDate").detached)

There are several techniques at play here. To sum up:

• SQL("CASE WHEN \(timeZoneOffset) IS NULL ...") uses SQL Interpolation.
• SQL(...).sqlExpression informs GRDB that this SQL snippet is an expression (not another type of SQL snippet). Doc.
• func(_ x: SQLExpressible) -> SQLExpression is a technique introduced in Embedding SQL in Query Interface Requests. SQLExpressible accepts columns but also regular swift values such as 123 or "foo". Prefer SQLSpecificExpressible when you want to restrict inputs to columns and other SQL-specific expressions.
• annotated is the method that extends the selection, so that you can process the extra value in fetched rows (SELECT *, extra). Doc.
• expr.forKey("localizedDate") builds expr AS localizedDate. Doc.
• Column("localizedDate").detached builds a "detached" column that will never be prepended by a table alias even if your request eventually uses associations. It is recommended for referring to columns built from expr.forKey(...) from the ORDER BY clause. Doc.

[dawilliams-gpsw]

@groue Thank you so much! I was starting to go down the path of a custom SQL function, so I'm glad you steered me back to the right path.

Thanks again for the feature rich library, it's amazingly well documented. There are so many features, that sometimes it's hard to know where to look. Cheers for the links. ❤️

[groue]

I agree the lib has become feature-rich. I'm also really happy to answer questions, give hints, help navigating in the documentation, and generally guide users towards a solution based on the existing APIs. So far I can stand the rhythm of questions, and the vast majority of users are like you, honestly curious and full of good will :)