public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: [email protected]
Cc: Jonas Gassenmeyer <[email protected]>
Cc: [email protected]
Subject: Re: NOW() function in combination with SET timezone
Date: Fri, 04 Jun 2021 10:04:40 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CADH0_2XcdBxDJMqa5q59gnPzpZO_f4E6A034R1+-G21-ByB2_w@mail.gmail.com>
	<[email protected]>

hubert depesz lubaczewski <[email protected]> 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

view thread (3+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: NOW() function in combination with SET timezone
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox