How to make Postgres return timestamps in a given timezone?

Question

I'm using sqlx 0.7.3 and time 0.3.34.

I have this struct:

#[derive(Debug, sqlx::FromRow)]
pub struct Game {
    pub id: String,
    pub created_at: time::OffsetDateTime,
    pub date_time: time::OffsetDateTime,
    pub score: i64,
    // and many other fields
}

I can read with queries like:

let query = "SELECT * from games"

let games = query.build_query_as::<Game>().fetch_all(db_connection).await?;

and I can insert with queries like:

let query = r#"INSERT INTO "games" ("id", ..columns...) VALUES ($1, $2, ...and other values...) RETURNING *"#;

let games = sqlx::query_as::<_, Game>(query)
    .bind(value)
    .bind(another_value)
    .fetch_one(db_connection)
    .await?;

and everything works (other more difficult queries too).

Now the issue.

The created_at and date_time fields are saved in the database as PostgreSQL's timestamptz type. Great.

But when I retrieve those fields I need to get them in the current user's timezone.

Example:

If the current user querying that games data is currently on timezone Europe/Berlin the backend code in Rust should work on that struct datetime fields on that timezone, not on UTC which apparently is the default using time::OffsetDateTime with sqlx.

I know I can do conversion on backend in Rust code (for example converting the time::OffsetDateTime to time::PrimitiveDateTime or using time-tz crate's methods), but I would like to do conversion directly in PostgreSQL.

I read I can use the AT TIME ZONE 'Europe/Berlin' PG's construct to do this, but what about all dates in all the query? Even when I use something like RETURNING * at the end of a PG's CTE?

I read I can use instead SET TIME ZONE 'Europe/Berlin' before the queries and I tried it but sqlx's author answered:

query_as uses the binary protocol which always outputs timestamps in UTC.

So I'm lost now.

Is it possible to let PostgreSQL return timestamps in the timezone I need, query by query?

Answer 1

you could use chrono

DateTime is timezone-aware and must be constructed from the TimeZone object, which defines how the local date is converted to and back from the UTC date. There are three well-known TimeZone implementations:

• Utc specifies the UTC time zone. It is most efficient.

use chrono::{DateTime, Utc};

pub struct Game {
    pub id: String,
    pub created_at: DateTime<Utc>,
    pub date_time: DateTime<Utc>,
}

Answer 2

Technically, you can set up a view or a rule to force PostgreSQL to quietly cast your timestamptz to timestamp using at time zone that'd use the current TimeZone setting configured by SET TIME ZONE 'Europe/Berlin' issued by the client. Demo at db<>fiddle:

create table test(tstz timestamptz);
insert into test values ('today') returning tstz;

tstz
2024-02-10 00:00:00+00

create view v_test as 
  select tstz at time zone current_setting('timezone',true) as tstz 
  from test;

select tstz from v_test;

tstz
2024-02-10 00:00:00

SET TIME ZONE 'Europe/Berlin';
select tstz from v_test;

tstz
2024-02-10 01:00:00

Is it possible to let Postgresql return the dates in the timezone I need, query by query?

The view above does just that, but I think you should reconsider if that's what you want.

OffsetDateTime/timestamptz are absolute - I think we established that last time you asked.

The reason SET TIME ZONE 'Europe/Berlin' doesn't affect what's happening in Rust after you pull it down from PostgreSQL is because it affects how PostgreSQL interprets timezone-unaware timestamps on input to timestamptz and how it prints timestamptz, timetz and their corresponding range types out to you. The underlying value is absolute, and however, wherever Rust reads it into OffsetDateTime, it remains absolute.

By design, no matter what time zone or location you obtain or inspect them in, makes absolutely no difference to the value. Your locale might affect default display format, still without affecting the value. If you want to dumb the value down to a relative timestamp, you do need to use time::PrimitiveDateTime in Rust or shift&strip in PostgreSQL using at time zone in the queries issued by the clients or in the view you'll force them to target instead of the actual table holding the actual timestamptz.

---

This:

query_as uses the binary protocol which always outputs timestamps in UTC.

contributes little to your problem. It might be an implementation detail how transfer is simplified - it's easier to force both ends to communicate in UTC and skip offset than to also transfer offset and shift accordingly. Unless you dumb down the value and switch to relative timestamps, the stored and transferred "form" of the timestamptz should not matter to you.

If you made it this far, you might be interested in .to_offset().

Answer 3

After SET TIME ZONE 'Europe/Berlin', all you need is a plain cast to timestamp:

SELECT id, created_at::timestamp, date_time::timestamp, score FROM games;

This cast uses the setting of the current session implicitly (which is why it's only "stable", not "immutable").

Works in a RETURNING clause as well:

INSERT INTO games (id, ...) VALUES ($1, ...)
RETURNING id, created_at::timestamp, date_time::timestamp, score;

Alternatively, omit the SET TIME ZONE command and coerce with the AT TIME ZONE construct explicitly. Then you can use a custom time zone for every expression. Again, the result is type timestamp for timestamptz input.

Basics:

• Ignoring time zones altogether in Rails and PostgreSQL

Most clients use the "text" protocol (which is usually more portable). The text representation of a timestamptz value reflects the current time zone setting:

est=> SET TIME ZONE 'UTC';
SET
test=> SELECT now() AS tstz, now()::timestamp AS ts;
              tstz             |            ts
-------------------------------+----------------------------
 2024-02-10 23:26:26.810186+00 | 2024-02-10 23:26:26.810186
(1 row)

test=> SET TIME ZONE 'EUROPE/BERLIN';
SET
test=> SELECT now() AS tstz, now()::timestamp AS ts;
              tstz             |            ts
-------------------------------+----------------------------
 2024-02-11 00:26:35.266357+01 | 2024-02-11 00:26:35.266357
(1 row)

The same timestamptz value has many different (but equivalent) text representations. So the problem only arises with the "binary" protocol, that does not superimpose the time zone information.