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?
