Description:
If a datetime field is defined as DATETIME or DATETIME(0) and an input string containing a fractional second, like '2026-10-26T09:37:43.432315+00:00', that require rounding up, the local time will not be applied. If the fraction is below .5, hence no rounding up required, local time is applied.
If DATETIME(1) or DATETIME(6) is used this problem does not appear.
How to repeat:
The MySQL server is running in a docker container, and uses the system timezone setup.
The following SQL script demonstrates this issue:
DROP TEMPORARY TABLE IF EXISTS test_rounding;
SET SESSION time_zone = '+01:00';
CREATE TEMPORARY TABLE test_rounding (
id INT AUTO_INCREMENT PRIMARY KEY,
eventtime DATETIME(0)
);
INSERT INTO test_rounding (eventtime) VALUES
('2026-10-26T09:37:43.432315+00:00'),
('2026-10-26T09:37:43.680160+00:00'),
('2026-10-26T09:37:44.144973+00:00'),
('2026-10-26T09:37:44.721467+00:00');
SELECT * FROM test_rounding ORDER BY id;
The output of this is:
id eventtime
1 2026-10-26 10:37:43
2 2026-10-26 09:37:44
3 2026-10-26 10:37:44
4 2026-10-26 09:37:45
So id 2 and 4 is not adjusted for local time.
Using a datetime definition with higher accuracy, like DATETIME(1), does not result in this behaviour.
This test is sent to the MySQL server using MySQL workbench 8.0.34
Suggested fix:
If the field definition is changed to DATETIME(1) the output is correct for all test values, but it should also work for DATETIME(0) since that is the same as DATETIME, which is probably widely used.
Description: If a datetime field is defined as DATETIME or DATETIME(0) and an input string containing a fractional second, like '2026-10-26T09:37:43.432315+00:00', that require rounding up, the local time will not be applied. If the fraction is below .5, hence no rounding up required, local time is applied. If DATETIME(1) or DATETIME(6) is used this problem does not appear. How to repeat: The MySQL server is running in a docker container, and uses the system timezone setup. The following SQL script demonstrates this issue: DROP TEMPORARY TABLE IF EXISTS test_rounding; SET SESSION time_zone = '+01:00'; CREATE TEMPORARY TABLE test_rounding ( id INT AUTO_INCREMENT PRIMARY KEY, eventtime DATETIME(0) ); INSERT INTO test_rounding (eventtime) VALUES ('2026-10-26T09:37:43.432315+00:00'), ('2026-10-26T09:37:43.680160+00:00'), ('2026-10-26T09:37:44.144973+00:00'), ('2026-10-26T09:37:44.721467+00:00'); SELECT * FROM test_rounding ORDER BY id; The output of this is: id eventtime 1 2026-10-26 10:37:43 2 2026-10-26 09:37:44 3 2026-10-26 10:37:44 4 2026-10-26 09:37:45 So id 2 and 4 is not adjusted for local time. Using a datetime definition with higher accuracy, like DATETIME(1), does not result in this behaviour. This test is sent to the MySQL server using MySQL workbench 8.0.34 Suggested fix: If the field definition is changed to DATETIME(1) the output is correct for all test values, but it should also work for DATETIME(0) since that is the same as DATETIME, which is probably widely used.