SET TIME ZONE in Pool creation doesn't work

[frederikhors]

As explained here I'm using the below code:

use sqlx::Executor;
use sqlx::postgres::PgPoolOptions;

let pool = PgPoolOptions::new()
  .after_connect(|conn, _meta| Box::pin(async move {
    conn.execute("SET TIME ZONE 'Europe/Berlin';").await?;

    let result: (time::OffsetDateTime,) = sqlx::query_as("SELECT current_timestamp").fetch_one(conn).await?;

    println!("Current Time in Europe/Berlin: {}", result.0);

    Ok(())
  }))
  .connect("postgres:// …").await?;

and the result is:

Current Time in Europe/Berlin: 2024-02-06 22:39:37.194022 +00:00:00

which is wrong because the current Berlin time is "23" not "22" (which is UTC).

My database is currently on UTC by default (from postgresql.conf file).

And it works if I use the below code in a Postgresql query:

SET TIME ZONE 'Europe/Berlin';
SELECT current_timestamp;

Where am I wrong?

Info

• SQLx version: 0.7.3
• SQLx features enabled: "macros", "postgres", "runtime-tokio", "time"
• Database server and version: Postgres v16.1
• Operating system: Windows 10
• rustc --version: rustc 1.75.0 (82e1608df 2023-12-21)

[abonander]

Not a bug: query_as uses the binary protocol which always outputs timestamps in UTC.

You should use LOCALTIMESTAMP with time::NaiveDateTime or perform the timezone conversion in your application code (my preference).

[frederikhors]

Thanks. I already understood on my own (after posting the question as often happens since the answer comes through the effort of reorganizing the thoughts) that the problem is time::OffsetDateTime and I can confirm that using time::PrimitiveDateTime works.

Thank you!

[frederikhors]

@abonander can I ask you what do you mean by:

perform the timezone conversion in your application code (my preference).

Do you mean using https://crates.io/crates/time-tz? Using the to_timezone() method?