How to do arithmetic with timestamps and intervals in Rust Diesel

Question

I've been trying to construct a diesel query with filter expressions that do arithmetic and comparison on timestamps and time intervals. I believe the following expressions are not possible to write using out-of-the-box diesel. Unfortunately, I've resorted to the sledgehammer approach of using the sql(...) function:

let query = videos::table
    .inner_join(events::table)
    .filter(sql(r#""events"."start_date" <= NOW() + interval '60 seconds'"#))
    .filter(sql(r#""events"."start_date" + ("videos"."duration" * interval '1 second') > NOW()"#))

My (abbreviated) schema:

table! {
    events (id) {
        id -> Int4,
        video_id -> Int4,
        start_date -> Timestamp,
    }
}

table! {
    videos (id) {
        id -> Int4,
        duration -> Float4,
    }
}

My questions:

1. Am I wrong? Can this be written in diesel with no custom types?
2. If this cannot be written in vanilla diesel, how can I break this up into a more type-safe, diesel-friendly expression? Which parts are custom and what traits do I need to implement?

Answer 1

Am I wrong? Can this be written in diesel with no custom types?

Yes this can be written with methods provided by diesel and some minor schema changes. See the corresponding documentation

Diesel requires that you only can add/substract intervals to timestamps. That implies that you need your schema a little bit to make this possible:

table! {
    videos (id) {
        id -> Int4,
        duration -> Interval,
    }
}

Your query then is written like this:

let query = videos::table
    .inner_join(events::table)
    .filter(events::start_date.le(now + 60.seconds()))
    .filter(now.lt(events::start_date + videos::duration))

If this cannot be written in vanilla diesel, how can I break this up into a more type-safe, diesel-friendly expression? Which parts are custom and what traits do I need to implement?

In theory it should be possible to implement that outside of diesel. The code would be very likely the same that is used to implement that in diesel itself. Probably you would need to use local types and more concrete impls. So if you cannot change the type of videos::duration it may be meaningful to go down this route.