CODEWITHSUNDEEP
sqlpostgresqltimestamp-with-timezone
yyuankm
yyuankm

Reputation: 365

Time zone for PostgreSQL by accessing through client and DBeaver

I have one table in the postgresql database (engine verion 10.11) with a column called resettime which has the datatype as timestamptz. The default timezone for the database is "Australia/Sydney".

The column resettime has the default value as timezone('UTC'::text, now()).

I ran the following command to set this column in DBEaver.

update mytable set resettime = timezone('UTC', now()) + interval '1 hour' where id = 1;

The current UTC time is 11:47, and the corresponding AEST time is 21:47

After running the above statement, I tried to get the resettime by the following statement in DBEaver.

select resettime from mytable where id=1

From this statement, I got 2020-09-14 12:47:25.

Then, I run the following command in DBEaver.

update mytable set resettime = resettime + interval '1 hour' where resettime < timezone('UTC', now());

After run this command, I check the resettime for the record with id=1, I found the resettime was kept as my expectation. It is normal.

However, when I ran the same update statement in my scala application, it updated the resettime as 2020-09-14 13:47:25. I have a feeling that resettime is still regarded as AEST time if I accessed it through my scala application. I do not want to change the default timezone settings to UTC.

How should I ensure my scala application also get the same behavior as my access through DBEaver? In the above example, I hope the resettime is not changed by my scala application to run the same update statement. Thanks!

Upvotes: 0

Views: 2082

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246113

The problem is that timezone('UTC', now()) converts the current timestamp to a timestamp without time zone – the result is the current timestamp as it would look on a clock set to UTC time.

Then, when you assign the result to resettime, which is a timestamp with time zone, the value it converted back, but this time according to your current setting of the timezome parameter (which is different from UTC).

So first you ask how a UTC clock looks right now, then you interpret that result in your current time zone. As a consequence, you end up with a value that is offset.

The simple and correct solution is not to convert between the two timestamp types at all, but stick with timestamp with time zone:

update mytable
set resettime = now() + interval '1 hour'
where id = 1;

That will do just what you want.

Upvotes: 3

Related Questions