How to query a Timestamptz with a Unix timestamp using Diesel?

Question

I can easily get the system Unix millisecond timestamp, but how can I convert it into something I can query with Diesel? The table in my schema has a Timestampz field (timestamp with time zone) that I need to compare to the Unix timestamp.

use diesel::sql_types::Timestamptz;

fn main() {
    let unix_time: i64 = 123456;
    let time: Timestamptz = ???
}

[dependencies]
diesel = { version = "1.4.7", features = ["postgres"] }

Answer 1

Timestamptz can be created from a PgTimestamp, chrono::NaiveDateTime or chrono::DateTime according to the docs.

I don't think there is a direct way to generate a timestamptz, and it's supposed to be opaque in most instances. Instead where a queries requires a Timetamptz you can pass a NaiveDateTime and load a NaiveDateTime back.

Now we use NaiveDateTime as it's the only one that can be created from a unix timestamp. DateTime requires an explicit timezone and can be generated from a NaiveDateTime.

use chrono::NaiveDateTime;

fn main() {
    let conn = get_connection();
    let ndt = NaiveDateTime::from_timestamp(0, 42_000_000);
    let (id, date) = users
        // assume a "created" column being a Timestamptz
        // you can just pass a NaiveDateTime.
       .filter(created.gt(ndt)) 
       .select((user_id, created))
       // We load back a NaiveDateTime and never deal with Timestamptz directly.
       .first::<(u64, NaiveDateTime)>(&conn)
       .expect("query failed");
}