2

I want to execute a PreparedStatement with a date added to it. First, I transform a LocalDate object into a java.sql.Date object and then add it to the PreparedStatement. The problem is that after setting the date with setDate(..) the statement contains the timezone as well.

So instead of: 

Select rating from BookRating where date_added='2017-09-16'

I get 

Select rating from BookRating where date_added='2017-09-16 +03'

And this does not work for me because all my rows in the SQL table contain only the date itself without timezone.

This is my code: 

LocalDate date = ...;
try (Connection conn = ConnectionFactory.getConnection()) {
    Date sqlDate = Date.valueOf(date);
    PreparedStatement pstmt = null;
    String SQL = "Select rating from BookRating where date_added=?";
    pstmt = conn.prepareStatement(SQL);
    pstmt.setDate(1, sqlDate);

    try (ResultSet rs = pstmt.executeQuery()) {
        if (rs.next()) {
            //does not enter here, no result entry
        }
    }

} catch (URISyntaxException e) {
    e.printStackTrace();
} catch (SQLException e) {
    e.printStackTrace();
}

This is the implementation of the method setDate(..)(taken from the JDK). In fact it uses Calendar to add a TimeZone to the query.

public void setDate(int i, Date d, Calendar cal) throws SQLException {
        this.checkClosed();
        if (d == null) {
            this.setNull(i, 91);
        } else if (this.connection.binaryTransferSend(1082)) {
            byte[] val = new byte[4];
            TimeZone tz = cal != null ? cal.getTimeZone() : null;
            this.connection.getTimestampUtils().toBinDate(tz, val, d);
            this.preparedParameters.setBinaryParameter(i, val, 1082);
        } else {
            if (cal == null) {
                cal = this.getDefaultCalendar();
            }

            this.bindString(i, this.connection.getTimestampUtils().toString(cal, d), 0);
        }
    }

My question is: how to remove the timezone from the query?

Csa77
  • 649
  • 13
  • 19
  • 1
    To avoid the timezone offset, try using a UTC calendar when setting the query parameter: `pstmt.setDate(1, sqlDate, Calendar.getInstance(TimeZone.getTimeZone("UTC")));`. – Andrew S Sep 17 '18 at 16:45
  • 1
    Another variation that might work: `Date sqlDate = Date.from(date.atStartOfDay(ZoneId.of("UTC")).toInstant());`. – Andrew S Sep 17 '18 at 16:57
  • 2
    Just found [this](https://stackoverflow.com/questions/530012/how-to-convert-java-util-date-to-java-sql-date) - use `setObject()` with the LocalDate instead of converting and using `setDate()`. – Andrew S Sep 17 '18 at 17:14
  • I'm facing the same issue, How did you resolve this @AndrewS – Manjunath Davanam May 27 '21 at 17:07
  • ```pstmt.setObject(1, LocalDate.parse("2021-05-27"), Types.DATE)``` with setObject changes also it's coming as ```'2021-05-27 +05:30'```. @AndrewS – Manjunath Davanam May 27 '21 at 17:26

0 Answers0