I'm trying to convert this query: ```SELECT *, ...

# exposed

[Allan Galarza]

I'm trying to convert this query:

SELECT *,
    CASE
        WHEN reminder = 0 THEN start-$1::interval
        WHEN reminder = 1 THEN start-$2::interval
        WHEN reminder = 2 THEN start-$3::interval
        WHEN reminder = 3 THEN start
    END as notification
FROM "event"
WHERE start >= (now() + $4) AND active AND reminder <= 3
ORDER BY notification ASC LIMIT 1

The part I'm stuck with is subtracting from the Instant type column start, I'm looking for some kind of "interval literal" or something that I can use to add a Duration in there.

val start = timestamp("start")

I have tried durationLiteral, but I'm still unsure on how to add it:

.When(EventTable.reminder eq 0, EventTable.start - (durationLiteral(1.hours.toJavaDuration())))
            .When(EventTable.reminder eq 1, EventTable.start.minus(durationLiteral(1.hours.toJavaDuration())))

[Mikhail Burshteyn]

If you’re using the exposed-java-time artifact, there is a durationLiteral function in there which should do the trick

[Allan Galarza]

Ooh yes, I just found durationLiteral , I'm still unsure how to apply it to the Then part of the query:

val case = Case()
    .When(EventTable.reminder eq 0, EventTable.start - (durationLiteral(1.hours.toJavaDuration())))
    .When(EventTable.reminder eq 1, EventTable.start)
    .When(EventTable.reminder eq 2, EventTable.start)
    .When(EventTable.reminder eq 3, EventTable.start)
    .Else(nullOp())

[Mikhail Burshteyn]

Oh, it seems like the timestamp-duration arithmetic operator isn’t mapped to Exposed.
You might write your own though, something along the lines of:

infix operator fun ExpressionWithColumnType<Instant>.minus(d: Expression<Duration>): CustomOperator<Instant> = CustomOperator("-", columnType, this, d)

[Allan Galarza]

Oooh, yeah that worked, as in it made it compile, however, the conversion to the query (postgresql) doesn't seem quite right, maybe I need to look for an interval type implementation 🤔

The query is being represented as this: ("event"."start" - '3600000000000')
(1 hour duration)