Dbeaver using local timezone for postgres remote connections

[mistersister]

Hello,
I have noticed that my Dbeaver is always using my local time on all connections to remote servers.

Im located in Spain (UTC+2) and I work on servers locate in Ghana (UTC). I have checked the postgres conifg on the remote server and it has its timezone config properly set up for Ghana (Africa/Accra).

When querying a remote server I always get the time/dates translated to my local UTC+2. and when updating a timestamp on the remote server, it will always get translated to the remote server time. This causes a lot of issues as you can imagine.

I have checked the "use native settings" option in the connection settings as explained here: #4181 (comment) but there is no difference at all: both times and timezones are still local.

This happens with all my remote servers on differents parts of the world.

This is my system info:

• Up to date Arch Linux (client) and CentOS6/7 running anything from postgres 9.2 to 9.6 on remote.
• DBeaver 6.0.5 (but happening since at least 6.0.0)
• OpenJDK8
• Standard JDBC driver.

If there is anything else I could/should provide, please let me know.

Thanks in advance for any help you can provide :)

[serge-rider]

This is a known issue in PG JDBC driver.

However jdcb driver team added Java8 dattime support recently and we going to use it next versions of DBeaver

https://jdbc.postgresql.org/documentation/head/java8-date-time.html

[mistersister]

great news indeed! awaiting eagerly for the next release. :)

[serge-rider]

Unfortunatelly I had a wrong impression. Although Java8 date api is supported by driver but all timezone based data types are actually fake types.
PG server still keeps everything in default timezone (UTC by default).
Client driver always converts all timestamp value into local timezone. You can change default session timezone to any value (by executing set time zone TZ). But generally this doesn't solve problem with mismatching server and client timezones.

I don't know how to handle time zones in postgresql better.

[serge-rider]

If anybody has any ideas how to do this better - please advise. And I'll reopen this ticket.

[art-bjss]

I also got bitten by this issue. To get an unambiguously correct UTC timestamp from a query, you can use the following:

For a field of type timestamptz:

to_char (<fieldname> at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

For a field of type timestamp (which was UTC when stored):

to_char (<fieldname>, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

These queries always give the same answer in DBeaver as in psql. (This assumes your database server is set to UTC, but it doesn't matter what your local computer clock is)

[StarpTech]

Please reopen. I run into the issue. The SQL database is UTC but all queries and information are displayed in my local timezone. This leads to very dangerous issues pgpartman/pg_partman#334

[StarpTech]

I could fix it with #13416 (comment)

[telford-tendys]

This is not a bug, it is normal for postgresql clients to use their local timezone. On a standard psql client you type show timezone; to see what your current settings are and set timezone='UTC'; to change settings for the current connection. If you try a simple select now(); it will include an offset indicating which timezone adjustment the server is applying to your session.

The only thing perhaps dBeaver might do, would be to make the current settings more obvious, to avoid confusion.

If you never want timezone adjustments, better use the timestamp data type (i.e. without time zone). This behaviour is well documented but often not well understood. Please read the following.

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES