From my experience, and some might disagree, I prefer to do db stores
purely in UTC and handle timezones in ORM or client side.
The only time I actually needed to store timezone information in a
dedicated column is when needing to convey that information to the end
user, for example "your plane will arrive at this date and time in this
destination timezone". The majority of other cases are just a
localization issue and don't require you to store the timezone info.
Having to rely on database to muck around with timezones or doing it in
session settings (which some advocate) is just asking for trouble in my
opinion.
On 9/21/21 9:35 AM, Tim Uckun wrote:
> 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
>>
>