PostgreSQL Question posted in
The official documentation can be found here.

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

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 the dates in the timezone I need, query by query?

Tags:

2

Answers


  1. 0

    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: time::DateTime<Utc>,,
    pub date_time: time::DateTime<Utc>,,
   
}
    Login or Signup to reply.
  2. 0

    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().

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search