Rounding operation of datetime field in MySQL

Preface

If I hadn't fallen into the trap, I probably wouldn't have known that the time field would be rounded up.

1. Background

The maximum time of the day is obtained through Java code and then stored in the database. The database table field format datetime retains 0 bits.

now.with(LocalTime.MAX)

A small line of code to get the maximum date of today.

I checked the database and found that the next day's time was actually stored there.

It looks like it’s rounded off!

2. Simulation test

After execution, take a look at the log:

It uses 2021-09-28T23:59:59.999999999, but strangely the database stores 2021-09-29 00:00:00.

Try using SQL directly:

This... is indeed rounded off.

Try MariaDB instead!

docker pull mariadb

docker run -d --name mariadb -p 33306:33306 -e "MYSQL_ROOT_PASSWORD=root" mariadb

docker exec -it mariadb bash

MariaDB discards extra digits directly!

3. Conclusion

If the value passed into MySQL time exceeds the precision range, it will be rounded.

If the value passed into MariaDB time exceeds the precision range, it will be discarded directly.

I stepped on a small pit. Finally, hard code it!

now.with(LocalTime.parse("23:59:59"))

Summarize

This is the end of this article about rounding of datetime fields in MySQL. For more information about MySQL datetime rounding, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!