When exactly is a TIMESTAMPTZ converted to the sessions time zone?

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: When exactly is a TIMESTAMPTZ converted to the sessions time zone?
Date: 2018-07-03 20:55:51
Message-ID: phgnok$vc9$1@blaine.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

A recent discussion around timestamptz behaviour has lead me to question my own understanding on how a TIMESTAMPTZ is converted to the session's time zone.

I assumed this conversion happens *on the server* before the value is sent to the client.

A co-worker of mine claims that this is purely a client side thing, and that the server will always send the "plain" UTC value that is stored in a timestamptz column.

The manual is ambiguous - at least to me

When a timestamp with time zone value is output, is always converted from UTC
to the current timezone zone, and displayed as local time in that zone

Does "is output" refer to the output on the client (after receiving a UTC value) or the sending of a converted value?

In this email: https://www.postgresql.org/message-id/19896.1152889217%40sss.pgh.pa.us

Tom Lane states:

They are converted to local time in the zone specified by the timezone
configuration parameter before being displayed to the client

but the "before being displayed" could also refer to a conversion on the server.

Could someone enlighten me, please?

Thomas

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2018-07-03 21:00:17 Re: When exactly is a TIMESTAMPTZ converted to the sessions time zone?
Previous Message Melvin Davidson 2018-07-03 18:59:57 Re: How to watch for schema changes