Subject: NOW() function in combination with SET timezone

From: Jonas Gassenmeyer <[email-masked]>
Date: Fri, 4 Jun 2021 09:53:20 +0200

Hello everyone,
i have a background in Oracle and it seems that dealing with time zones is
treated a bit different in PostgreSQL. I started experimenting with NOW()
and timezone settings and understand that (at least the *display* in the
client) can be changed by the SET command. What I don't understand is, what
data/info will be sent from my client to the database server when...

   1. ...storing a timestamp value in a column (INSERT)
   2. ...comparing now() with another column in the WHERE clause

For 1.) it would help if I have similar options like the DUMP() function in
Oracle (is there an equivalent that would give me a hint what has been
*physically* stored in a column?)

For 2.) it would help if You could confirm my current understanding:

   - 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.
   - For further comparison with any time stamp columns in a table it would
   use the converted timestamp (UTC timezone of the database server).
   - It would not make a difference if I compare NOW() to a column of type
   timestamp instead of timestampTZ (assuming that I know what timezone was
   used to insert into the column)

Thank You so much in advance and happy Friday!
---
Jonas

---

From: hubert depesz lubaczewski <[email-masked]>
Date: Fri, 4 Jun 2021 12:35:57 +0200

>    I send data to the server my timestamp would get converted to the timezone

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

>    used to insert into the column)

If you ensured that you always make conversion to utc, and store utc,
then yes. But then - you will be on your own with calculations of things
like daylight savings time.

timestamptz doesn't store time zone information, but makes sure that
when you enter data it is converted to common TZ, and then does
conversion to user timezone (which can be based on server timezone) on
select.

depesz

---

From: Tom Lane <[email-masked]>
Date: Fri, 04 Jun 2021 10:04:40 -0400

> 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