Can't get to Use Option<DateTime<Local>> for a NULL able TIMESTAMPTZ column in postgres.

[smf8]

Hello,
Sorry, this might be a trivial question. But I'm a newbie in Rust and I'm trying to map the following Database structure into a rust struct using query_as! macro.

Schema:

CREATE TABLE test(
id SERIAL NOT NULL,
description TEXT NOT NULL,
deleted_at TIMESTAMPTZ NULL DEFAULT NULL,
PRIMARY KEY (id),
)

Rust struct:

struct Test {
    id: i32,
    description: String,
    deleted_at: Option<chrono::DateTime<Local>>,
}

When I try to use the given struct in a query_as! macro like:

sqlx::query_as!(
            Test,
            r#"
        SELECT * FROM test WHERE id = $1
        "#,
            id
        )

I'll encounter the following compile error :

error[E0277]: the trait bound `std::option::Option<chrono::DateTime<chrono::Local>>: std::convert::From<std::option::Option<chrono::DateTime<chrono::Utc>>>` is not satisfied

The error will be gone by using deleted_at: Option<chrono::DateTime<Utc>> and It won't throw an error if the deleted_at field is not NULLABLE (not wrapped in an Option). But I'm just curious if there is a way to make it work :?

Thank you beforehand

[smf8]

For future visitors of this Q&A. The problem can be solved by overriding column type at select query.

So instead of

        SELECT * FROM test WHERE id = $1

You should use

        SELECT id,description, deleted_at as "deleted_at: DateTime<Local>" FROM test WHERE id = $1

[xpe]

Thanks. Some questions as of August 2024:

• is this a change from a previous version of SQLx?
• is this a workaround?
• is there a way to avoid the workaround?
• is there a "golden path" w.r.t. using timestamp/datetime fields?

[xpe]

@smf8 Your suggestion to add "deleted_at: DateTime<Local>" can help, but I don't recommend it unless it is a last resort. (I'm not sure, but it might be necessary in some cases depending on whether you use Option and have a nullable column.)

I want to point out something that has changed between sqlx 0.7 and 0.8: make sure you enable the "chrono" feature but not the "time" feature. See the explanation at #2697.

sqlx = { version = "0.8.0", features = ["chrono", "postgres", "..."] }
# adapt as needed; e.g. I also use "runtime-tokio", "tls-rustls"

With the features shown above, for my code (different from your example), SQLx does not require the verbose annotation to make a DateTime<> type work.

P.S. This page lists which Rust types correspond to which PostgreSQL column types: https://docs.rs/sqlx/latest/sqlx/postgres/types/index.html