PostgreSQL, Timezones, and DBeaver

Time zones are an unfortunately complex subject when dealing with PostgreSQL. You may be running your local time zone on your on-premises server or on your own laptop. Or you may be using the time zone of your server’s physical location. And you may have set all your servers to UTC. And all are valid approaches, depending on your circumstances.

DBeaver users know it is a very advanced tool for database work. But it is easy to get into time zone issues, as the default time zone for your session is taken from your client machine. But this can be adjusted.

UTC?

UTC or Universal Time Coordinated is a time zone standard used as a basis for all time zones worldwide. It is a constant time scale and does not change for Daylight Saving Time (DST). The benefits include streamlined cross-region data synchronization, easier debugging, accurate time-based transaction ordering, and scalability for global applications. In distributed systems, storing data in UTC ensures that logs and transactions are ordered correctly, regardless of which geographic region recorded the action. There are many more reasons to use UTC, which will be ignored for brevity.


PostgreSQL knows how to adjust for situations like where you are in a North American time zone, and the cluster of servers is in EMEA. Most client programs, when sending a timestamp, get it converted by the PostgreSQL server. But sometimes that does not happen, and you need to make adjustments.


Checking Your Time Zone


You can check your time zone in PostgreSQL with the SHOW timezone; command. In my case, I am in Texas, which is in the America/Chicago time zone.


The SHOW timezone; command and its output

















You can see the time zone offset at the end of the output from select now();



Here we see the date, the time,
and the offset from the current
time and UTC.


  



















In this case, on my laptop’s local instance, I am using the local time

zone. Which is now -5 hours away from UTC.


This is the session timezone, set by the client's connection information. I can double-check that by querying  SELECT * FROM pg_settings WHERE name = 'TimeZone';


Here we see the connections, the time zone, and that the server
is getting the time zone information from the connection.
















The Problem


What if your server is running in a different time zone, such as UTC? We will assume the server is performing a large number of transitions and is replicated to other servers in other time zones.  But when you connect to that server, your session will report the time zone from your system, which is probably not in UTC. You want UTC!


Remember, most of the time, PostgreSQL will normalize or convert the incoming (session) time zone to the server's time zone (UTC or another time zone used by the system). So you should not have to worry (please read this as 'hey, you had better double check this just to make sure!') 

What if you have a situation where you would want to use UTC everywhere?


Why is UTC better in your scenario?


  • Consistency across replicas and time zones: All servers (primary and replicas) see the exact same absolute timestamps for events, regardless of their local OS time zone or continent. This avoids subtle bugs during replication, failover, or cross-server queries.

  • High-transaction reliability: timestamptz (timestamp with time zone) internally stores everything as UTC anyway. Using UTC as the session/server default eliminates ambiguity when inserting or reading times without explicit offsets.

  • Avoids DST and timezone rule changes: Local time zones can shift due to daylight saving time or political changes. UTC never does, making historical data and comparisons reliable.

  • Replicated environments: Different replicas in other time zones could interpret or display the same data differently if they rely on local server time. UTC removes that risk.


Key rule: Use the timestamptz (or timestamp with time zone) data type for all timestamp columns that represent moments in time (e.g., created_at, transaction_time, audit logs). Avoid plain timestamp (without time zone) unless you truly need a "local wall-clock time" without timezone context (rare for transactional systems).


Store in UTC, convert to user-local time only in the application/presentation layer (e.g., your app code, reports, or UI). This is the consensus best practice for distributed/global systems.


JDBC (Java): Add ?timezone=UTC or similar to the connection URL, or execute the SET statement.


DBeaver and Setting Time Zones


So, you have found that you have a time zone issue. The system you use is in a different time zone than the server. And you want to make sure the two time zones match. You can configure DBeaver to always use a specific time zone, or set the time zone on a connection-by-connection basis.

Note: I am using UTC for the examples below, but you can also use your time zone of choice.

Option 1 - Default to UTC Always


If you want all connections to default to UTC, you can do so in Preferences.

DBeaver (which uses the PostgreSQL JDBC driver) often defaults to your local machine's timezone (Austin, Texas = likely America/Chicago or similar). This can cause timestamptz values to be interpreted or displayed incorrectly compared to the server. There is no automatic "use server's timezone" option because the JDBC driver cannot reliably query the server's default timezone setting at connect time.


You can set the timezone in Windows →Preferences →User Interface →Timezone and select UTC.


DBeaver's options for time zones























UTC is near the bottom. Restart DBeaver or reconnect to apply the change.


After making the change and reconnecting, it has taken effect.


+0000 is UTC










Option 2 - On A Per-connection Basis


Maybe you only want to make sure specific servers use UTC. Right-click on that connection, select Edit Connection. Select the Driver Property tab.


The Driver Property tab under Edit Connection






















Select the Green Plus sign


Select the Green Plus sign at the bottom





You can add the setting under User Properties


















Now add serverTimezone and set the value to UTC.


Be sure to use the Test Connection button, just because you want to make sure it works!














How to Verify it Worked


After reconnecting, run these in a SQL editor:

  • SHOW timezone;           -- Should show 'UTC' (or whatever you set)

  • SELECT now();            -- Should return time in the session timezone

  • SELECT now() AT TIME ZONE 'UTC';

  • SELECT current_setting('timezone');


Compare the output to what you get in psql on the server.


Summary


Time zones can be problematic when using PostgreSQL.  DBeaver allows you to handle time zone issues on a per-connection or global basis. 




Comments

Post a Comment

Popular posts from this blog

How PostgreSQL's Aggregate FILTER Will Spoil You

Using PostgreSQL will definitely spoil you. I recently had to use another database and found, to my chagrin, that aggregate filters were not included in their implementation of Structured Query Language. Specifically, there was no FILTER clause available. FILTER? To illustrate my dependency, let me start with some simple data. demo=# create table z (a int, b int, c int); CREATE TABLE demo=# insert into z values (1,10,100),(2,20,200),(3,30,300),(4,40,400); INSERT 0 4 demo=# select a,b,c from z;  a | b  |  c   ---+----+-----  1 | 10 | 100  2 | 20 | 200  3 | 30 | 300  4 | 40 | 400 (4 rows) demo=#  If I wanted the overall number of rows, I could use a simple COUNT(*). If I wanted an overall number of rows where one of the columns was of a certain value, I could add the logic for getting those rows to the WHERE clause. What if I wanted both?  Do I need two queries to do the work separately? Maybe a materialized view or a CTE (Common Tabl...
Read more

Incremental Backups in PostgreSQL 17

 The old adage that a DBA or SRE is only as good as their last backup is true.  PostgreSQL 17 added the ability to combine multiple incremental backups with a full backup to provide a complete data dictionary to recover a failed system. It is very easy to use. This is a quick example of using incrementals. I recommend watching this video by Robert Haas for more details and some discussion of backup strategies. Step 1 - Enable WALL Summerization The incremental backups require Write Ahead Log Summarization. It is likely not on your instance by default. But it is easy to enable. demo=# alter system set summerize_wal = 'on'; ALTER SYSTEM demo=# select pg_reload_conf(); pg_reload_conf  ----------------  t (1 row) Step 2 - Full Backup The examples below are much simpler than you will run into in real life.  The scenario is that we make a full backup on Sunday and do incrementals the rest of the week. Wednesday through Saturday are omitted below.  stoker@ThinkPa...
Read more

Is the future of MySQL PostgreSQL (Or MariaDB, or TiDB, or ...)?

 I am not intentionally trying to upset anyone with this blog post or minimize the efforts of many brilliant people whom I admire. However, I connected with several people over the 2025 holidays who all had the same question: What is the future of MySQL? At the upcoming FOSDEM conference, several events will discuss this subject and push a particular solution.  And in several ways, they are all wrong. Oracle has not been improving the community edition for a long time now. They have laid off many of their top performers in the MySQL group. We got almost a good decade and a half out of Oracle's stewardship of the "world's most popular database", and we should be thankful for that. However, now that time is over, it is time to consider future options that will involve no updates, CVEs, or innovation for what is the MySQL Community Edition. There are several choices available. Nothing! The first choice is nothing. Many folks run old, end-of-life versions of MySQL. There ...
Read more