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.albe@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
>
