Re: NOW() function in combination with SET timezone

hubert depesz lubaczewski <depesz(at)depesz(dot)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