How to get the UTC offset from a TIMESTAMPTZ value in Rust using postgres-rs with time-rs 0.3?

Question

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?

Hoashis

Generally speaking, it's a bad idea for your clients to rely upon the server's timezone setting, as the server admin could change the setting with unexpected results: the postgres crate discourages such reliance by always returning TIMESTAMP WITH TIME ZONE values in UTC, as you are observing. You will (almost) always know what timezone you want values in irrespective of the server's setting, so you need merely perform conversions after obtaining each value from the database. – Grisby

In the (extremely) rare event that you actually do need values in "the server's timezone, no matter what it happens to be", you could feed the result of something like select extract( timezone from now() ) to UtcOffset::parse for onward use. – Grisby

Thanks for your suggestions. I assumed that I could set timezone in the connection's session, so that PostgreSQL would handle the conversions from UTC, just as with psql. But for reasons unknown to me I seem to have to repeat the process on the application server. I did try extract(timezone ..) before, but that got me into serious trouble with DST changes. I failed to take into account that timestamps are calculated incorrectly when the DST offset was different from the current time zone. – Hoashis

Related to #65144316 – Hoashis

Answer 1

Use chrono-tz-rs

Postgres stores TIMESTAMPTZ in UTC just like TIMESTAMP. The only difference is that the first type denotes that any values should be interpreted locally. So values are converted to / from UTC before being stored and displayed.

Based on the docs one could expect that values of type TIMEZONETZ would be returned in the locale that was set on the database connection:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the TimeZone configuration parameter before being displayed to the client.

You might interpret "before being displayed to" as "before being sent to", but in practice it boils down to "before displayed by the client".

From this thread:

Clients like psql or pgAdmin or any application communicating via libpq (like Ruby with the pg gem) are presented with the offset for the current time zone or according to a given time zone [..]

In practice, the conversion does not take place server side, but client side through libpq. Because you're not using any direct bindings to libpq in Rust, the TIMESTAMPTZ values are in UTC still.

The easiest solution is to go with chrono 0.4, because on top of chrono-rs there is chrono-tz-rs, which enables the conversion from UTC using the IANA database:

// row: &postgres::Row
Option<stdDateTime<Local>> = row.get(index);

Hoashis

From this thread it seems that the conversion is done with libpq, and the only 'problem' is that in Rust, with postgres, the libpq library is not used. Which is why you'll have to do it yourself in Rust via chrono or jiff. – Hoashis

Why don't you just call now.to_offset(offset) with your desired offset (obtained, if necessary, from select extract(timezone from now()) and UtcOffset::parse)? – Grisby

Ok, but how can I know the offset? Timezone Europe/Amsterdam changes DST throughout the year from CEST to CET and back. – Hoashis

select extract(timezone from now()) should give it as offset from UTC. – Grisby

Can you elaborate why you're interested in the server's timezone anyway? Why isn't the desired timezone known to the client? – Grisby

This TIMESTAMP: is a timestamp in UTC, is wrong. It is the literal timestamp at whatever the TimeZone is set to at the time. – Schinica

offtopic: You are aware that postgres version 0.19.9 doesn't exists? This is not the official documentation. Check the official one: postgresql.org/docs/current/index.html – Sedgemoor

Sorry for the name confusion. WIth postgres I meant the crate postgres aka postgres-rs. Where I've writte PostgreSQL I meant PostgreSQL. – Hoashis