I have a java ZonedDateTime which I am trying to insert into a MySQL Date column (the DBA refuses to change the type - the date should have a time component since it is an audit date/time for when an event occurs - I would like to have the java code retain the time in case someday the DBA changes their mind).
When MyBatis tries to insert the ZonedDateTime into the Date column, I receive the following error:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '\xAC\xED\x00\x05sr\x00\x0Djava.time.Ser\x95]\x84\xBA\x1B''H\xB2\x)C\x00\....' for column auditDate at row 1
What would be the best way to get MyBatis to insert the ZonedDateTime into the MySQL Date column? Do I need to cast to a java.util.Date or some other type to successfully perform the insertion?
myPreparedStatement.setObject( … , myZonedDateTime.toLocalDate() ) ;
As commented, the ZonedDateTime class is not mapped in JDBC.
The JDBC 4.x specification is defined in the Java Community Process (JCP). See JSR 221: JDBC™ 4.0 API Specification. You’ll find links there for the JDBC 4.2 and 4.3 specs.
The reason for the lack of support for ZonedDateTime is that the SQL standard recognizes only offset-from-UTC, not time zones. The SQL standard type TIMESTAMP WITH TIME ZONE is a misnomer. The authors actually meant offset, as they were not savvy with date-time handling.
See a similar Answer I recently posted. It offers more detail.
The DATE type in MySQL represents a date only, no time of day, no offset, no time zone. So you could store the date portion of your ZonedDateTime.
LocalDate ld = zdt.toLocalDate() ;
myPreparedStatement.setObject( … , ld ) ;
To record a moment, a specific point on the timeline, use the SQL standard type TIMESTAMP WITH TIME ZONE. In MySQL, the matching type is TIMESTAMP.
Do I need to cast to a java.util.Date
Never use the terribly flawed legacy date-time classes. These were entirely supplanted by the modern java.time classes.