public inbox for [email protected]  

help / color / mirror / Atom feed

From: Jonas Gassenmeyer <[email protected]>
To: [email protected]
Subject: NOW() function in combination with SET timezone
Date: Fri, 4 Jun 2021 09:53:20 +0200
Message-ID: <CADH0_2XcdBxDJMqa5q59gnPzpZO_f4E6A034R1+-G21-ByB2_w@mail.gmail.com> (raw)

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

view thread (3+ messages)  latest in thread

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]
  Subject: Re: NOW() function in combination with SET timezone
  In-Reply-To: <CADH0_2XcdBxDJMqa5q59gnPzpZO_f4E6A034R1+-G21-ByB2_w@mail.gmail.com>

* 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