How to display a TIMESTAMPTZ in local time using Rust using postgres-rs with time-rs 0.3?

I'm trying to display a local time using postgres-rs, with time-rs 0.3 instead of chrono-rs 0.4. But with the timezone set to Europe/Amsterdam, all I get is UTC timestamps with no timezone information to correct it.

From psql show timezone; select now(); reads Europe/Amsterdam and 2024-10-01 10:38:36.63829+02.

But when I try the same thing in Rust:

// mut conn: PooledConnection<PostgresConnectionManager<MakeTlsConnector>>

let mut tz: Option<String> = None;
if let Ok(r) = conn.query_one("show timezone", &[]) { tz = r.get(0); }
println!("timezone: {:?}", tz);

let mut now: Option<OffsetDateTime> = None;
if let Ok(r) = conn.query_one("select now()", &[]) { now = r.get(0); }
println!("now: {:?}", now);

I get:

timezone: Some("Europe/Amsterdam")
now: Some(2024-10-01 8:41:49.767418 +00:00:00)

Without an appriopriate value from OffsetDateTime.offset(), how can I display the local time correctly?

Solution

With time-rs

After enabling the offset-local feature flag you can make use of time::local_offset_at() to display the timestamp with UTC offset from the OS:

let mut now: Option<OffsetDateTime> = None;
if let Ok(r) = conn.query_one("select now()", &[]) { now = r.get(0); }
let offset = UtcOffset::local_offset_at(now)?;  // get OS offset
println!("now: {:?}", now.to_offset(offset););

With SQL

Alternatively, you can ask the database to do the conversion for you:

select current_timestamp::timestamp as now

The database will convert the timestamptz to timestamp according to its timezone setting. You can set it to your local time zone with a value from pg_timezone_names at the:

cluster level in postgresql.conf with timezone = 'Europe/Amsterdam'
database level with alter database [dbname] set timezone = 'Europe/Amsterdam';
or at the connection session level with set timezone = 'Europe/Amsterdam';

With Chrono-rs

First enable the with-chrono-0_4 feature flag in Cargo.toml, then you can do:

let mut now: Option<DateTime<Local>> = None;
if let Ok(r) = conn.query_one("select now()", &[]) { now = r.get(0); }
println!("now: {:?}", now);

Background

The postgres-rs crate communicates with Postgres via a binary protocol, which communicates all timestamptz values in UTC. Normally they are converted via the C-library libpq. But postgres-rs does not rely on this library. Therefore, timestamptz values in Rust must be made local by other means. The default way is to rely on the OS, as demonstrated with time-rs and chrono-rs.

When you want the client (say, website or WASM app) to be able to set a custom timezone without having to rely on JavaScript too much, you can either add conversions to all SQL queries and let Postgres do the heavy lifting, or make use of chrono-tz.

Chrono-tz might not be ideal for every use case. It imports the entire IANA timezone database into your code by default. If you only need a limited number of time zones, you can apply a regex filter. The executable must be updated when timezone information changes to stay synced. This might cause differences with how timestamptz values are presented in the database.