I'm facing quite weird situation working on pure JDBC Java code connected to Postgres DB.
The SQL used for PreparedStatement is relatively simple:
SELECT * FROM test t INNER JOIN ....
WHERE t.id = ? AND (?::TIMESTAMP IS NULL OR t.date + t.time <= ?::TIMESTAMP WITHOUT TIME ZONE)
t.date is [date NOT NULL]
t.time is [time without time zone NOT NULL]
So, Java code to prepare and set parameters is as following:
PreparedStatement statement = connection.prepareStatement(SQL);
statement.setInt(1, id);
if (date== null) {
you are changing the type of the parameter here when you setNull Types.DATE. use Types.TIMESTAMP.
statement.setNull(2, Types.DATE);
statement.setNull(3, Types.DATE);
}
else {
statement.setTimestamp(2, Timestamp.valueOf(date));
statement.setTimestamp(3, Timestamp.valueOf(date));
}
ResultSet rs = statement.executeQuery();
The issue is: though Java code sets date parameters to PreparedStatement correctly (checked with logging) i.e. $1 = '29', $2 = '2017-01-14:13:00', $3 = '2017-01-14:13:00' , but actual query performed by PostgreSQL server (logging) SOMETIMES shows parameters used GMT DETAIL: parameters: $1 = '29', $2 = '2017-01-14', $3 = '2017-01-14'. Strangest thing that in 95% cases of same request with different params the output (behaviour and parameters) is correct, i.e. parameters are set and seen as DATE + TIME both from API and PostgreSQL. Moreover, repeteadly passing problematic parameters finally gets right query's behavior and output, sometimes after few tries or Java application restart. Worth mentioning, Java app does not do any "caching" or data modification - it's simple REST API for DB calls.
I'm not sure if it might be something very simple (JDBC/DB configuration) or a bug in postgres JDBC driver - since there is no other sofware to blame. Java code seemingly does everything right but PostgresSQL SOMETIMES receives different parameters (date instead of date + time).
I've tried to enable postgres JDBC driver logging as it's said in documentation but did not manage it to operate with Spring Boot logging.