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...
...storing a timestamp value in a column (INSERT)
...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)