Re: Timestamp with vs without time zone.

On Tue, 2021-09-21 at 18:00 +1200, Tim Uckun wrote:
> I am hoping to get some clarification on timestamp with time zone.
>
> My understanding is that timestamp with time zone stores data in UTC
> but displays it in your time zone.

That is correct.
When a timestamp is rendered as string, it it shown in the time zone
specified by the current setting of the "timezone" parameter in your
database session.

> Does this also work on queries? If
> I query between noon and 2:00 PM on some date in time zone XYZ does pg
> translate the query to UTC before sending it to the server?

Yes.

> To provide context I have the following situation.
>
> I have a data file to import. All the dates in the time zone
> pacific/auckland. My app reads the data , does some processing and
> cleaning up and then saves it to the database.
>
> The language I am using creates the time data type with the right time
> zone. The processing is being done on a server which is on UTC, the
> database server is also on UTC.  I am pretty sure the ORM isn't
> appending "at time zone pacific/Auckland" to the data when it appends
> it to the database.
>
> So does the database know the timestamp is in auckland time when the
> client is connecting from a server on UTC?

It does, but only if you set "timezone" appropriately in the database
session. You could use ALTER ROLE to change the default setting for a
database user, but it might be best to set that from the application.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com