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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Thomas Kellerer
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


Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Thomas Kellerer <[hidden email]> writes:
> 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.

It's done in the datatype's output conversion function.

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

Your co-worker is certainly wrong so far as text output is concerned.
If you retrieve data in binary format, though, it looks like you get
the raw (un-rotated) timestamp value, so that any conversion would have
to be done on the client side.

                        regards, tom lane

Reply | Threaded
Open this post in threaded view
|

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

Bruce Momjian
On Tue, Jul  3, 2018 at 05:00:17PM -0400, Tom Lane wrote:

> Thomas Kellerer <[hidden email]> writes:
> > 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.
>
> It's done in the datatype's output conversion function.
>
> > 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.
>
> Your co-worker is certainly wrong so far as text output is concerned.
> If you retrieve data in binary format, though, it looks like you get
> the raw (un-rotated) timestamp value, so that any conversion would have
> to be done on the client side.

Wow, I am kind of surprised by that.  Do any other data types have this
behavior?

--
  Bruce Momjian  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Reply | Threaded
Open this post in threaded view
|

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

Paul Jungwirth
On 08/09/2018 01:49 PM, Bruce Momjian wrote:

> On Tue, Jul  3, 2018 at 05:00:17PM -0400, Tom Lane wrote:
>> Thomas Kellerer <[hidden email]> writes:
>>> 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.
>>
>> It's done in the datatype's output conversion function.
>>
>>> 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.
>>
>> Your co-worker is certainly wrong so far as text output is concerned.
>> If you retrieve data in binary format, though, it looks like you get
>> the raw (un-rotated) timestamp value, so that any conversion would have
>> to be done on the client side.
>
> Wow, I am kind of surprised by that.  Do any other data types have this
> behavior?

This isn't related to binary-vs-string format, but I think it's often
overlooked that timestamptz considers your timezone not just to
stringify the value, but also to truncate it:

db=> create table t (ts timestamp, tstz timestamptz);
CREATE TABLE
Time: 3.154 ms
db=> set timezone='America/Los_Angeles';
SET
Time: 0.303 ms
db=> insert into t (ts, tstz) values ('2018-06-09 19:00:00', '2018-06-09
19:00:00');
INSERT 0 1
Time: 2.653 ms
db=> select ts, date_trunc('day', ts), tstz, date_trunc('day', tstz) from t;
          ts          |     date_trunc      |          tstz          |
     date_trunc
---------------------+---------------------+------------------------+------------------------
  2018-06-09 19:00:00 | 2018-06-09 00:00:00 | 2018-06-09 19:00:00-07 |
2018-06-09 00:00:00-07
(1 row)

Time: 0.438 ms
db=> set timezone='UTC';
SET
Time: 0.227 ms
db=> select ts, date_trunc('day', ts), tstz, date_trunc('day', tstz) from t;
          ts          |     date_trunc      |          tstz          |
     date_trunc
---------------------+---------------------+------------------------+------------------------
  2018-06-09 19:00:00 | 2018-06-09 00:00:00 | 2018-06-10 02:00:00+00 |
2018-06-10 00:00:00+00
(1 row)

Are there any other places where timestamptz consults your timezone?

--
Paul              ~{:-)
[hidden email]

Previous Thread Next Thread