Description:
When a timestamp literal string includes both fractional seconds and a timezone offset, the timezone conversion fails if the fractional part of the second is > .5. The conversion works as expected when the fraction is <= .499.
In examples 1 and 2, from what I understand, the TIMESTAMP keyword in fact creates a DATETIME(3) literal. This issue only affects TIMESTAMP literals - DATETIME literals behave correctly and perform timezone conversions as expected.
Additionally, in examples 7 and 8, note that if an operation is performed on the timestamp before insertion, the timezone conversion works correctly. I suspect this is because in example 6, the fractional seconds are rounded immediately, whereas in example 8, the timezone offset is applied first, then the addition occurs, and only afterward are the fractional seconds rounded when inserting into a column with lower precision.
How to repeat:
### The server is in UTC.
+--------------------+---------------------+-----------+
| @@GLOBAL.time_zone | @@SESSION.time_zone | version() |
+--------------------+---------------------+-----------+
| UTC | UTC | 8.0.41 |
+--------------------+---------------------+-----------+
### Creating test table and data.
CREATE TABLE `test_tz` (
`id` int NOT NULL,
`ts` timestamp NOT NULL
) ENGINE=InnoDB;
INSERT INTO test_tz (id, ts)
VALUES
(1, TIMESTAMP '2025-04-01 00:00:00.499+06:00'),
(2, TIMESTAMP '2025-04-01 00:00:00.500+06:00'),
(3, CAST('2025-04-01 00:00:00.499+06:00' AS DATETIME)),
(4, CAST('2025-04-01 00:00:00.500+06:00' AS DATETIME)),
(5, '2025-04-01 00:00:00.499+06:00'),
(6, '2025-04-01 00:00:00.500+06:00'),
(7, DATE_ADD('2025-04-01 00:00:00.499+06:00', INTERVAL 15 MINUTE)),
(8, DATE_ADD('2025-04-01 00:00:00.500+06:00', INTERVAL 15 MINUTE));
### It produces the following records. All rows were converted correctly, except row 6.
+----+---------------------+
| id | ts |
+----+---------------------+
| 1 | 2025-03-31 18:00:00 |
| 2 | 2025-03-31 18:00:01 |
| 3 | 2025-03-31 18:00:00 |
| 4 | 2025-03-31 18:00:01 |
| 5 | 2025-03-31 18:00:00 |
| 6 | 2025-04-01 00:00:01 |
| 7 | 2025-03-31 18:15:00 |
| 8 | 2025-03-31 18:15:01 |
+----+---------------------+
Suggested fix:
Time zone conversions should occur consistently, regardless of the fractional seconds. Ideally, TIMESTAMP and DATETIME behavior should also be consistent.
Description: When a timestamp literal string includes both fractional seconds and a timezone offset, the timezone conversion fails if the fractional part of the second is > .5. The conversion works as expected when the fraction is <= .499. In examples 1 and 2, from what I understand, the TIMESTAMP keyword in fact creates a DATETIME(3) literal. This issue only affects TIMESTAMP literals - DATETIME literals behave correctly and perform timezone conversions as expected. Additionally, in examples 7 and 8, note that if an operation is performed on the timestamp before insertion, the timezone conversion works correctly. I suspect this is because in example 6, the fractional seconds are rounded immediately, whereas in example 8, the timezone offset is applied first, then the addition occurs, and only afterward are the fractional seconds rounded when inserting into a column with lower precision. How to repeat: ### The server is in UTC. +--------------------+---------------------+-----------+ | @@GLOBAL.time_zone | @@SESSION.time_zone | version() | +--------------------+---------------------+-----------+ | UTC | UTC | 8.0.41 | +--------------------+---------------------+-----------+ ### Creating test table and data. CREATE TABLE `test_tz` ( `id` int NOT NULL, `ts` timestamp NOT NULL ) ENGINE=InnoDB; INSERT INTO test_tz (id, ts) VALUES (1, TIMESTAMP '2025-04-01 00:00:00.499+06:00'), (2, TIMESTAMP '2025-04-01 00:00:00.500+06:00'), (3, CAST('2025-04-01 00:00:00.499+06:00' AS DATETIME)), (4, CAST('2025-04-01 00:00:00.500+06:00' AS DATETIME)), (5, '2025-04-01 00:00:00.499+06:00'), (6, '2025-04-01 00:00:00.500+06:00'), (7, DATE_ADD('2025-04-01 00:00:00.499+06:00', INTERVAL 15 MINUTE)), (8, DATE_ADD('2025-04-01 00:00:00.500+06:00', INTERVAL 15 MINUTE)); ### It produces the following records. All rows were converted correctly, except row 6. +----+---------------------+ | id | ts | +----+---------------------+ | 1 | 2025-03-31 18:00:00 | | 2 | 2025-03-31 18:00:01 | | 3 | 2025-03-31 18:00:00 | | 4 | 2025-03-31 18:00:01 | | 5 | 2025-03-31 18:00:00 | | 6 | 2025-04-01 00:00:01 | | 7 | 2025-03-31 18:15:00 | | 8 | 2025-03-31 18:15:01 | +----+---------------------+ Suggested fix: Time zone conversions should occur consistently, regardless of the fractional seconds. Ideally, TIMESTAMP and DATETIME behavior should also be consistent.