Re: Timestamp parameter gets sent as date

On 22 September 2017 at 06:43, Genya <genyap(at)online(dot)ua> wrote:

> 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.
>
>
Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com