Java PostgreSQL library seems to convert dates to local time zone automatically

Question

PostgreSQL 14.6
Ubuntu 22.04

I am using postgresql-42.5.4.jar which I downloaded from pgJDBC. I use this library to get data from a database and display it on a website running locally. The web server and database server are both running on the same machine.

The database server's time zone is UTC. The system's time zone is America/Chicago.

I have a table that contains a column of type timestamp with time zone.

The data is inserted into the table by a separate C++ program that uses a completely different library. In order to insert the timestamps, it uses a Unix timestamp and the to_timestamp () function, like this:

insert into my_table (my_time) values (to_timestamp (1654321098));

The timestamp is retrieved from the table as a string and passed back to the website as is. A comment below suggested using the java.sql.OffsetDateTime class but I don't know where that class would be used. Here is the Java code I am using:

String query = "select my_time from my_table";
ResultSet result_set = db_connection.createStatement ().executeQuery (query);
String result = result_set.getString ("my_time");

When I query this column from my command line database client, it shows me the dates in UTC, which is what I would expect because that is the time zone the server is using. This simple query would look like this:

select my_time from my_table;

While still in my command line client, if I want to display that column in my local time, I have to modify my query like this:

select my_time at time zone 'America/Chicago' as my_time from my_table;

But I started noticing that the website was displaying incorrect times. I temporarily had it print its query to the screen so I could look at it in my command line client. The result was not the same. In order to display the time in my local time on the website, I had to remove the at time zone 'America/Chicago' part of the query, which does not seem to make sense and does not produce the same result in the command line client, and it also makes the code less portable if I were to move it to a system using a different database library.

Does the Java driver for PostgreSQL automatically convert timestamp fields to local time? If it does, is there a way to turn that feature off? If it doesn't, then what could be causing the different results I get between the JDBC library and my command line client?

Answer 1

You should set the timezone parameter correctly in your database session, then the timestamp with time zone will be converted to your time zone automatically:

SET timezone = 'America/Chicago';

Answer 2

Your Java program (or application) runs in a Java virtual machine (JVM). The JVM uses the system's (the server it is running on) timezone, by default.

You can find out what timezone your Java program is running on within your Java code: java.util.TimeZone.getDefault() and TimeZone.getDefault().getID() prints an output like "America/Chicago", for example.

In your code, String result = result_set.getString("my_time"); the result value is dependent upon the JVM's timezone.

Depending upon your systems operations you have various ways of controlling (or setting) the JVM's timezone. You can use a suitable one for your operational environment. For example, I tried a Java program using the Java runtime system property user.timezone, where the UTC is the timezone for the program run:

OS prompt> java -Duser.timezone=UTC -cp .;postgresql-42.7.3.jar PgDb.java

From psql shell:

• show timezone; // UTC
• Inserted into database column type timestamp with time zone a value: to_timestamp(1284352323)
• Select query shows the column as: 2010年09月13日 04:32:03+00

My Java program running with -Duser.timezone=UTC option for the code String result = result_set.getString("my_time");, returns result as: 2010年09月13日 04:32:03+00.

Without the -Duser.timezone=UTC option returns result as: 2010年09月13日 10:02:03+05:30 (where the default timezone was "Asia/Calcutta").

NOTE: I was running Postgres 14, Java 17 and Postgres Java driver 42.7.3.