All geek questions in one place

Request time zone - server and db client server have different time zones

(Continued: PostgreSQL time zone repository )

I am using PostgreSQL 9.x, the db server has a default time zone of +9 , for example, it has a table containing a timezone column.

There is a Java program on the same server as db, it requests data from the database. Both the server on which db is located and the client (browser) of the Java server program are in the -8 time domain.

My questions:

  • Will the client (brower) show the original datetime or the adjusted datetime from 9 - (-8) = 17 hours earlier?
  • If I want to get the same data in the browser client as the original db value, should I change the time zone for the Java server server or client machine, or both?
    (I know it is better to make db and its server the same time zone, but suppose for some reason they do not set the same thing).
+1
source share
1 answer

You always get the same data in browser client as the db original value . There are many different ways to display the same point in time (in different time zones). Two examples (textual representations of the timestamptz value):

 '2012-03-05 20:00:00+03' '2012-03-05 18:00:00+01'

The same meaning.

As for Postgres, setting the server time zone for this is completely irrelevant. The only relevant one is session setup. And the only thing that changes is the display of value. It is always the same point in time.

So, just set the time zone in your session to get the appropriate textual representation:

Another option would be the AT TIME ZONE design. To get timestamp as a local timestamp without time zone :

 SELECT my_timestamptz_column AT TIME ZONE '-8';

When working with table that contains a column of timezone type this may be the best approach, since the time zone can vary for each row.

Again, all this in the reference answer:

+2
source

Source: https://habr.com/ru/post/1260318/

More articles:


All Articles