We are upgrading our project from Mysql 5 to Mysql 8. there is weird issue coming in db queries in date handling.

On MySQL 5, while saving data to db, the date used to be saved with ‘yyyy-MM-dd HH:mm:ss’ format without caring about milliseconds.

But on Mysql 8, the date is getting saved after rounding milliseconds, suppose if Date is ‘2024-07-18 09:39:34.927’ then in db it will be saved as ‘2024-07-18 09:39:35’.

This is causing issue in get calls in DAO. Below is Dao call for reference.

@Query(“SELECT pts FROM TableName pts WHERE columnA = ?1 
       + ” AND columnB = ?2")
Entity getData(int val1, Date date);

This is how date column is defined in entity

@Basic
@Column(name = "column_a", nullable = false)
@Temporal(TemporalType.TIMESTAMP)
private Date columnA;

And this is SQl structure for date column.

`column_a` datetime NOT NULL,

If the call is made with date = ‘2024-07-18 09:39:34.927’ then it is returning null.

I found one solution to avoid date rounding i.e sqlmode=TIME_TRUNCATE_FRACTIONAL. This is preventing rounding, but the issue still persists as while date comparison in JPA query, it is still considering milliseconds. For example, data is saved as ‘2024-07-18 09:39:34’ but comparison will be done based on ‘2024-07-18 09:39:34.927’ value of date field.

However this can be solved by manually changing date format before passing to query. But there are many such JPA calls, is there any configuration level way to not honour milliseconds in JPA queries for MYSQL 8?
Or, is there any right way to resolve this issue?