Geographically dispersed servers, PostgreSQL and JPA

Question

My TIMEZONE database server is UTC. I have Java servers in different time zones that interact with the database. I naively found that if I have a TIMESTAMP field, there is no way for me to install it from the JPA code of a Java server in UTC format. I assume that I naively believed that if JDBC would always send dates with TZ information, and the database server could accordingly convert or exit based on its time zone. But no.: (

Here rub. I can have Java servers in different time zones interacting with this database. If something happens at 9 pm on the east coast and at 18:00 on the west coast, I would like both of these events to be stored in the database at the same time. Spirit, right?

So what solution, I suppose, can put all my Java servers in UTC. But it seems like there should be a less difficult decision. I guess I could also set TIME ZONE on every transaction, but ugh.

I know that I will get some answers saying that I should only store long databases, and although I appreciate the veracity of this answer, I want to keep the TIMEZONE and DATE fields in my database tables. Therefore, I am looking for other answers. Thanks.

Answer 1

The solution is to use the TIMESTAMPTZ fields for these points in time. Something like “when the user was created” should never be stored in the TIMESTAMP field, because by default it does not contain TZ information. The JDBC driver processes them completely randomly. For example, consider the following:

Suppose your JVM is in the America / Los_Angeles zone when your database server is in UTC.

Then create the following table:

CREATE TABLE test ( id INTEGER, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, tswz TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); 

If you choose from PSQL:

  INSERT INTO test(id) values(1); 

You will get the same values ​​for "ts" and "tswz". Both will be the current UTC time. However, if you are making an EXACT EXACT query with Java using the JDBC driver, then “ts” will be the current time in Los Angeles, and “tswz” will be the UTC time.

I do not know HOW the driver passes the JVM time zone to the server in this case, because by default we use the field on the server. They say that they do not set the time zone for the session, but they should. In any case, if you use the TIMESTAMPTZ field, you will get the same moments from any JVM, no matter what time zone it is in.