From: Tom Lane <tgl@sss.pgh.pa.us>
To: depesz@depesz.com
cc: Jonas Gassenmeyer <gassenmj@gmail.com>, pgsql-novice@lists.postgresql.org
Subject: Re: NOW() function in combination with SET timezone
In-reply-to: <20210604103557.GA31655@depesz.com>
References: 
 <CADH0_2XcdBxDJMqa5q59gnPzpZO_f4E6A034R1+-G21-ByB2_w@mail.gmail.com>
 <20210604103557.GA31655@depesz.com>
Comments: In-reply-to hubert depesz lubaczewski <depesz@depesz.com>
	message dated "Fri, 04 Jun 2021 12:35:57 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <144133.1622815480.1@sss.pgh.pa.us>
Date: Fri, 04 Jun 2021 10:04:40 -0400
Message-ID: <144134.1622815480@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/144134.1622815480%40sss.pgh.pa.us>
Precedence: bulk

hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Fri, Jun 04, 2021 at 09:53:20AM +0200, Jonas Gassenmeyer wrote:
>> - Let's assume my client has set the timezone to Tokyo and the server is
>> using UTC.
>> - Even if calling NOW() and the display in my client is Tokyo time, once
>> I send data to the server my timestamp would get converted to the timezone
>> of the server.

> Assuming you're talking about timestamptz - value on disk is in UTC.

Yeah.  There's no such thing as a "server timezone" for this purpose.
(The server does have a "log timezone" setting, but that's for log
messages; it shouldn't ever affect client-visible behavior.)  The
string you send to the server is assumed to be in the timezone defined
by the client-controllable timezone setting, and it is converted to UTC
for storage.  (Of course, if the string you send contains an explicit
zone name or UTC offset, we believe that instead; but in any case the
bits on disk represent a time in UTC.)

Later, if you try to read out the value, the value is converted to
a string according to the then-prevailing timezone setting.

Internal operations like now() and comparisons are unbothered by
the timezone setting --- they just deal in UTC timestamps.

> timestamptz doesn't store time zone information,

This is the main thing you need to realize when coming to Postgres
from another DBMS.  It's not per SQL spec, I think, but we're unlikely
to change its behavior at this late date.

			regards, tom lane