From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Timestamp with vs without time zone. |
Date: | 2021-09-21 07:35:20 |
Message-ID: | CAGuHJrNeP8H-J9LD-pwO5gaNhSSKSgfn=oBKrH5-ifyoy+WgrA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)albe(at)cybertec(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2021-09-21 07:35:47 | Re: Possibilities for optimizing inserts across oracle_fdw foreign data wrapper |
Previous Message | asaba.takanori@fujitsu.com | 2021-09-21 07:29:07 | Synchronous logical replication |