Not localhost datetime but database datetime while inserting entity to database-CodePudding

I am having trouble inserting a new record into my PostgreSQL database. I am using Spring Boot with Spring Data JPA and Hibernate. I am connecting to an external database. The time on this server is different than on my localhost. When I insert a new entity into the database, this record is inserted with the date and time from my local host and not from the database server. I know I can set a datetime zone in application.properties, but if I change the database connection URL I may not know what time is set on the database server. I know I can get the now () time from the database before saving it, but I'm sure there is another solution. Thanks for help.

CodePudding user response：

The best way to deal with this situation is to use the data type timestamp with time zone in PostgreSQL. Then timestamps are stored as absolute time, independent from the time zone (I know that this sort of contradicts the name of the data type). Then all you need to do is to set the database parameter timezone to the correct client time zone in each database session.

Let's assume that the database server is in New York, but we are in Calcutta:

CREATE TABLE tstest (
   id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   t timestamp with time zone NOT NULL
);

SET timezone = 'Asia/Kolkata';

INSERT INTO tstest (t) VALUES (current_timestamp);

SELECT * FROM tstest;

 id │                t                 
════╪══════════════════════════════════
  1 │ 2022-02-23 14:00:19.555188 05:30
(1 row)

Now somebody in Los Angeles accesses the data:

SET timezone = 'America/Los_Angeles';

SELECT * FROM tstest;

 id │               t               
════╪═══════════════════════════════
  1 │ 2022-02-23 00:30:19.555188-08
(1 row)

So everybody sees the correct timestamp, independent from where the server is located.