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?

[mehcode]

Postgres can only return timestamps as UTC. The typename timestamptz is a bit of a misnomer.

Technically you can return timestamps without a timezone using the timestamp like

SELECT (now()::timestamp at time zone 'UTC')::timestamp

This would become a time::PrimitiveDateTime which would be "at the right time" but without the timezone/offset encoded into the value.

You can also get around this by returning time as ISO-8601.. something like

SELECT to_char (now()::timestamp at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

It's a bit verbose but you could create a function to_iso8601 or similar in a migration so it's DRY at least.