I have a query where the clause is based on any data uploaded after a certain date:
SELECT *
FROM table_data da
WHERE da.uploaded_at > '2023-06-27 14:59:22.947058 +00:00'::TIMESTAMP
When I execute it thought Intellij IDEA using Database Tool plugin it returns:
ID | UPLOADED_AT |
---|---|
1 | 2023-06-29 19:49:18.103044+00 |
But, when I execute it though a Java class using a JDBC connection, it returns:
ID | UPLOADED_AT |
---|---|
1 | 2023-06-29 21:49:18.103044+02 |
Where this returned timezone is defined?
Environment:
try (Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement preparedStatement = conn.prepareStatement(sql))
{
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next())
{
System.out.println(resultSet.getString("id") + "|" + resultSet.getString("uploaded_at")));
}
}
(...)
It turned out the different behavior was caused by the JDBC connector using the client's time zone (UTC+2) and for some reason neither Database Tools nor PGAdmin I executed the query use the client's time zone. They either set it as UTC (matching my database configuration) or somehow use the database time zone.
In order to have the same behavior I managed to set the time zone on my Java class execution passing a VM option
-Duser.timezone=UTC