Обсуждение: NOW() function in combination with SET timezone
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...
- ...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)
Thank You so much in advance and happy Friday!
---
Jonas
Jonas
On Fri, Jun 04, 2021 at 09:53:20AM +0200, Jonas Gassenmeyer wrote: > - 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. Assuming you're talking about timestamptz - value on disk is in UTC. > - 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) 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
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Fri, Jun 04, 2021 at 09:53:20AM +0200, Jonas Gassenmeyer wrote:
>> - 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.
> 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