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

[frederikhors]

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?

[jhpratt]

time itself has no support for SQL queries. It is potentially a question for sqlx, but I can't say for certain.