NOW() function in combination with SET timezone

From: Jonas Gassenmeyer <gassenmj(at)gmail(dot)com>
To: pgsql-novice(at)lists(dot)postgresql(dot)org
Subject: NOW() function in combination with SET timezone
Date: 2021-06-04 07:53:20
Message-ID: CADH0_2XcdBxDJMqa5q59gnPzpZO_f4E6A034R1+-G21-ByB2_w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message hubert depesz lubaczewski 2021-06-04 10:35:57 Re: NOW() function in combination with SET timezone
Previous Message Bruce Momjian 2021-06-03 17:32:02 Re: Database copy