Is this SET TIME ZONE supposed to work like this with sqlx?

Question

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)

Answer 1

Current UTC time in Berlin is the same as current UTC time in Tōkyō. The timestamp is correct and the text you added to it isn't incorrect - it's just that the whole thing is pretty confusing, because the location has nothing to do with what time it currently is if you're operating on timezone-aware timestamps, which denote absolute point in time.

It goes without saying that doing set time zone in a query has no impact on your Rust app time zone offset configuration - time uses system's offset. If your aim was for your Rust app to print out the 2024-02-06 23:39:37.194022 +01:00:00, you would have to change your system's or app's time zone. The value of timestamp with time zone you save to OffsetDateTime remains unaffected by that, it's just output format that changes - again, those timestamps are absolute.

If you're looking for an "observed time", there's at time zone returning just that, which means stripping the timezone info to give you "a picture of a clock" taken at a location in a given timezone. db<>fiddle:

select now();

now
2024-02-07 07:23:15.314852+00

set time zone 'Europe/Berlin';
select now();

now
2024-02-07 08:23:15.316844+01

set time zone 'Europe/Berlin';
select now() at time zone 'UTC'

timezone
2024-02-07 07:23:15.317335

set timezone='utc';
create table test(ts timestamp, tstz timestamptz);
insert into test values
('2024-02-07 12:00+01', '2024-02-07 12:00+01'),
('2024-02-07 12:00+02', '2024-02-07 12:00+02'),
('2024-02-07 12:00+03', '2024-02-07 12:00+03'),
('2024-02-07 12:00+04', '2024-02-07 12:00+04')
returning *,ts   at time zone 'UTC' "ts at tz",
            tstz at time zone 'UTC' "tstz at tz";

ts	tstz	ts at tz	tstz at tz
2024-02-07 12:00:00	2024-02-07 11:00:00+00	2024-02-07 12:00:00+00	2024-02-07 11:00:00
2024-02-07 12:00:00	2024-02-07 10:00:00+00	2024-02-07 12:00:00+00	2024-02-07 10:00:00
2024-02-07 12:00:00	2024-02-07 09:00:00+00	2024-02-07 12:00:00+00	2024-02-07 09:00:00
2024-02-07 12:00:00	2024-02-07 08:00:00+00	2024-02-07 12:00:00+00	2024-02-07 08:00:00

set timezone='Australia/Sydney';
select *,ts   at time zone 'Australia/Sydney' "ts at tz",
         tstz at time zone 'Australia/Sydney' "tstz at tz"
from test;

ts	tstz	ts at tz	tstz at tz
2024-02-07 12:00:00	2024-02-07 22:00:00+11	2024-02-07 12:00:00+11	2024-02-07 22:00:00
2024-02-07 12:00:00	2024-02-07 21:00:00+11	2024-02-07 12:00:00+11	2024-02-07 21:00:00
2024-02-07 12:00:00	2024-02-07 20:00:00+11	2024-02-07 12:00:00+11	2024-02-07 20:00:00
2024-02-07 12:00:00	2024-02-07 19:00:00+11	2024-02-07 12:00:00+11	2024-02-07 19:00:00