Description:
INSERT seems to round the fraction part of DATETIME and TIMESTAMP columns, instead truncating them.
This can be serious when not having enough precision for a datetime column, resulting in events have the next second/minute/day/month or even year, instead of the current one! Like if something is inserted as '2023-12-31 23:59:59.511111' it is stored as '2024-01-01 00:00:00'.
For time it does not make sense to round up.
NOW() and UTC_TIMESTAMP() does truncate.
How to repeat:
create table t (dt datetime, dt6 datetime(6), ts timestamp, ts6 timestamp(6));
insert into t values (now(6), now(6), now(6), now(6));
select sleep(0.5);
insert into t values (now(6), now(6), now(6), now(6));
insert into t values (now(), now(), now(), now());
select sleep(0.5);
insert into t values (now(), now(), now(), now());
select * from t;
Results in:
+---------------------+----------------------------+---------------------+----------------------------+
| dt | dt6 | ts | ts6 |
+---------------------+----------------------------+---------------------+----------------------------+
| 2024-10-01 16:40:57 | 2024-10-01 16:40:57.399706 | 2024-10-01 16:40:57 | 2024-10-01 16:40:57.399706 |
| 2024-10-01 16:40:58 | 2024-10-01 16:40:57.903779 | 2024-10-01 16:40:58 | 2024-10-01 16:40:57.903779 | <= Rounded UP dt and ts!!
| 2024-10-01 16:40:57 | 2024-10-01 16:40:57.000000 | 2024-10-01 16:40:57 | 2024-10-01 16:40:57.000000 | <= Note that NOW() does truncate!
| 2024-10-01 16:40:58 | 2024-10-01 16:40:58.000000 | 2024-10-01 16:40:58 | 2024-10-01 16:40:58.000000 |
+---------------------+----------------------------+---------------------+----------------------------+
Suggested fix:
Never round time, always truncate it!
