0

I'm trying to query some data from a MySQL database using the following query:

String query = "SELECT * FROM my_table WHERE Timestamp > '" + startDate + "'";

The column Timestamp is DATETIME.

The variable startDate is an object of type ZonedDateTime, obtained as follows:

Date start = ...;
ZonedDateTime startDate= ZonedDateTime.ofInstant(start.toInstant(),ZoneId.of(cachedResource.getHistorizationParameters().getTimeZone()));

This gives me the following ZonedDateTime: 2021-08-17T09:22:29+02:00[Europe/Madrid] , which is making the queries fail when I execute them:

java.sql.SQLException: Incorrect DATETIME value: '2021-08-17T09:22:29+02:00[Europe/Madrid]'

Other ZonedDateTimes have worked in the past, I don't know what is going wrong and I would really appreciate some directions on solving this problem.

Thanks!

Luna
  • 155
  • 9
  • MySQL does not accept datetime literals with intermediate `T` and timezone tail. Use STR_TO_DATE() with proper pattern for converting, or provide a value which matches [MySQL's datetime literal format](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html). – Akina Aug 17 '21 at 08:22
  • 1
    Do not use the error-prone legacy Date-Time API. Also, use `PreparedStatement` to avoid SQL injection. Check the duplicate questions to learn how to use the modern Date-Time API as well as `PreparedStatement`. – Arvind Kumar Avinash Aug 17 '21 at 08:40
  • If you are serious about that being a timestamp, you should also use the `timestamp` data type of MySQL. And then `OffsetDateTime` in Java (`Instant` may work too and is fine if it does). If you cannot change the data type in MySQL, use `LocalDateTime` in Java. Under no circumstances transfer the value as a string from Java to the query. Transfer an `OffsetDateTime`, `Instant` or `LocalDateTime` as shown in the linked original question. – Ole V.V. Aug 17 '21 at 19:44

0 Answers0