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
