It seems like it would be so much more useful if the timestamp with time zone type actually stored the time zone in the record.
On Tue, Sep 21, 2021 at 7:25 PM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > > 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 >
