Description:
DATE_ADD rounds nanoseconds, but only for the first argument.
SELECT
DATE_ADD('2017-12-31 23:59:59.9999999', INTERVAL 0 SECOND) AS c70,
DATE_ADD('2017-12-31 23:59:59', INTERVAL 0.9999999 SECOND) AS c07;
+---------------------+----------------------------+
| c70 | c07 |
+---------------------+----------------------------+
| 2018-01-01 00:00:00 | 2017-12-31 23:59:59.999999 |
+---------------------+----------------------------+
Looks inconsistent.
Note, the dyadic function TIMESTAMP rounds nanoseconds for both arguments:
SELECT
TIMESTAMP('2017-12-31 23:59:59.9999999', '00:00:00') AS c70,
TIMESTAMP('2017-12-31 23:59:59', '00:00:00.9999999') AS c07;
+----------------------------+----------------------------+
| c70 | c07 |
+----------------------------+----------------------------+
| 2018-01-01 00:00:00.000000 | 2018-01-01 00:00:00.000000 |
+----------------------------+----------------------------+
How to repeat:
SELECT
DATE_ADD('2017-12-31 23:59:59.9999999', INTERVAL 0 SECOND) AS c70,
DATE_ADD('2017-12-31 23:59:59', INTERVAL 0.9999999 SECOND) AS c07;
Suggested fix:
Please fix DATE_ADD() to round nanoseconds for both arguments.
Description: DATE_ADD rounds nanoseconds, but only for the first argument. SELECT DATE_ADD('2017-12-31 23:59:59.9999999', INTERVAL 0 SECOND) AS c70, DATE_ADD('2017-12-31 23:59:59', INTERVAL 0.9999999 SECOND) AS c07; +---------------------+----------------------------+ | c70 | c07 | +---------------------+----------------------------+ | 2018-01-01 00:00:00 | 2017-12-31 23:59:59.999999 | +---------------------+----------------------------+ Looks inconsistent. Note, the dyadic function TIMESTAMP rounds nanoseconds for both arguments: SELECT TIMESTAMP('2017-12-31 23:59:59.9999999', '00:00:00') AS c70, TIMESTAMP('2017-12-31 23:59:59', '00:00:00.9999999') AS c07; +----------------------------+----------------------------+ | c70 | c07 | +----------------------------+----------------------------+ | 2018-01-01 00:00:00.000000 | 2018-01-01 00:00:00.000000 | +----------------------------+----------------------------+ How to repeat: SELECT DATE_ADD('2017-12-31 23:59:59.9999999', INTERVAL 0 SECOND) AS c70, DATE_ADD('2017-12-31 23:59:59', INTERVAL 0.9999999 SECOND) AS c07; Suggested fix: Please fix DATE_ADD() to round nanoseconds for both arguments.