CODEWITHSUNDEEP
mysqlspring-bootjpaspring-data-jpamysql-8.0
amangautam1
amangautam1

Reputation: 840

Handling Date comparisons in MYSQL 8 and Spring boot without Milliseconds

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?

Upvotes: 0

Views: 45

Answers (1)

amangautam1
amangautam1

Reputation: 840

After dedicating time to debugging, I identified a specific MySQL variable, sendFractionalSeconds, that resolved the problem. This variable must be explicitly set to false in order to prevent the rounding of milliseconds when querying or saving data to the database.

To implement this solution, please set the variable as follows:

sendFractionalSeconds=false

For further details, please refer to the MySQL documentation: Connector/J and Fractional Seconds.

Upvotes: 0

Related Questions