MIME-Version: 1.0
From: Jonas Gassenmeyer <gassenmj@gmail.com>
Date: Fri, 4 Jun 2021 09:53:20 +0200
Message-ID: 
 <CADH0_2XcdBxDJMqa5q59gnPzpZO_f4E6A034R1+-G21-ByB2_w@mail.gmail.com>
Subject: NOW() function in combination with SET timezone
To: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ebe89105c3ebfc64"
Archived-At: 
 <https://www.postgresql.org/message-id/CADH0_2XcdBxDJMqa5q59gnPzpZO_f4E6A034R1%2B-G21-ByB2_w%40mail.gmail.com>
Precedence: bulk

--000000000000ebe89105c3ebfc64
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ebe89105c3ebfc64
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello everyone,</div><div>i have a background in Orac=
le and it seems that dealing with time zones is treated a bit different in =
PostgreSQL. I started experimenting with NOW() and timezone settings and un=
derstand that (at least the *display* in the client) can be changed by the =
SET command. What I don&#39;t understand is, what data/info will be sent fr=
om my client to the database server when...<br></div><div><ol><li>...storin=
g a timestamp value in a column (INSERT)<br></li><li>...comparing now() wit=
h another column in the WHERE clause</li></ol><div>For 1.) it would help if=
 I have similar options like the DUMP() function in Oracle (is there an equ=
ivalent that would give me a hint what has been *physically* stored in a co=
lumn?)</div><div><br></div><div>For 2.) it would help if You could confirm =
my current understanding:</div><div><ul><li>Let&#39;s assume my client has =
set the timezone to Tokyo and the server is using UTC.</li><li>Even if call=
ing NOW() and the display in my client is Tokyo time, once I send data to t=
he server my timestamp would get converted to the timezone of the server.</=
li><li>For further comparison with any time stamp columns in a table it wou=
ld use the converted timestamp (UTC timezone of the database server).</li><=
li>It would not make a difference if I compare NOW() to a column of type ti=
mestamp instead of timestampTZ (assuming that I know what timezone was used=
 to insert into the column)</li></ul><div>Thank You so much in advance and =
happy Friday!<br></div></div></div><div><div dir=3D"ltr" class=3D"gmail_sig=
nature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr"><div><div dir=
=3D"ltr"><div><div dir=3D"ltr"><span style=3D"font-family:Verdana;font-size=
:12px;background-color:rgb(255,255,255)">---</span><br style=3D"font-family=
:Verdana;font-size:12px;background-color:rgb(255,255,255)"><span style=3D"f=
ont-family:Verdana;font-size:12px;background-color:rgb(255,255,255)">Jonas =
<br></span></div></div></div></div></div></div></div></div>

--000000000000ebe89105c3ebfc64--