Daniel Adamski added a comment - 2017-11-21 03:27 Detected using JOOQ, which generates SQL with nanosecond precision for subsecond part. Reported here: https://github.com/jOOQ/jOOQ/issues/6820

Elena Stepanova added a comment - 2017-11-30 00:41 I agree there is inconsistency here, even though maybe it's not an intuitive one. Let's take for example MINUTE_SECOND vs MINUTE_MICROSECOND (it's the same for DAY_MICROSECOND , but shorter). The format of the interval values described both for MariaDB and MySQL as MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' The punctuation character between integers can be anything. So, it's not a floating point between seconds and microseconds, it's just a separator between an integer number of seconds and integer number of microseconds. Same for a separator between minutes and seconds. And it does indeed work like that for minutes and seconds: 1.5 is not one minute and a half, it's 1 minute and 5 seconds MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.5' minute_second); + ----------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.5' minute_second) | + ----------------------------------------------------------------+ | 1000-01-01 00:01:05 | + ----------------------------------------------------------------+ 1 row in set (0.00 sec) 1.50 is not the same as 1.5, it's 1 minute and 50 seconds MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.50' minute_second); + -----------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.50' minute_second) | + -----------------------------------------------------------------+ | 1000-01-01 00:01:50 | + -----------------------------------------------------------------+ 1 row in set (0.00 sec) 1.5000000 is not the same as 1.5, it's 1 minute and 5000000 seconds MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.5000000' minute_second); + ----------------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.5000000' minute_second) | + ----------------------------------------------------------------------+ | 1000-02-27 20:54:20 | + ----------------------------------------------------------------------+ 1 row in set (0.00 sec) Same exact thing happens with microseconds when they grow above 6 digits 01.01.5000000 is 1 minute, 1 second and 5000000 microseconds == 1 minute and 6 seconds MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.01.5000000' minute_microsecond); + ------------------------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.01.5000000' minute_microsecond) | + ------------------------------------------------------------------------------+ | 1000-01-01 00:01:06.000000 | + ------------------------------------------------------------------------------+ 1 row in set (0.00 sec) PROBLEM Thus, it's reasonable to expect – from the definition and by analogy – that 01.01.5 MINUTE_MICROSECOND would be 1 minute, 1 second and 5 microseconds; but it's not so: MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.01.5' minute_microsecond); + ------------------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.01.5' minute_microsecond) | + ------------------------------------------------------------------------+ | 1000-01-01 00:01:01.500000 | + ------------------------------------------------------------------------+ 1 row in set (0.00 sec) I don't know whether it needs to be fixed, or the specification needs to be refined, leaving it to serg to decide.

Daniel Adamski added a comment - 2017-11-30 01:35 Thus, it's reasonable to expect – from the definition and by analogy – that 01.01.5 MINUTE_MICROSECOND would be 1 minute, 1 second and 5 microseconds Apparently it was like that in some cases (specifically, when day was missing) and it was changed to always "expand" microseconds part to 6 digits. See (old) mysql bug#36466 .

Lukas Eder added a comment - 2017-11-30 09:49 Observe the behaviour of the following: select date_add('1000-01-01 00:00:00', interval '0.5' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.50' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.500' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.5000' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.50000' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.500000' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.5000000' minute_microsecond) ; Quite unexpected...

Alexander Barkov added a comment - 2018-02-07 12:09 - edited https://mariadb.com/kb/en/library/date-and-time-units/ says: The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, time_quantity is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character. Therefore, interval '+0 00:00:01.300000000' day_microsecond reported in https://github.com/jOOQ/jOOQ/issues/6820 is treated as: 1 second plus 300000000 microseconds, which gives 301 seconds total (i.e. 5 minutes and one second) This query gives a correct result: select date_add('0001-01-01 00:00:00', interval '+0 00:00:01.300000000' day_microsecond); +-----------------------------------------------------------------------------------+ | date_add('0001-01-01 00:00:00', interval '+0 00:00:01.300000000' day_microsecond) | +-----------------------------------------------------------------------------------+ | 0001-01-01 00:05:01.000000 | +-----------------------------------------------------------------------------------+ Note, this query: MariaDB [test]> SELECT DATE_ADD('0001-01-01 00:00:00', INTERVAL '1 00:00:01.30000000' DAY_SECOND); +----------------------------------------------------------------------------+ | DATE_ADD('0001-01-01 00:00:00', INTERVAL '1 00:00:01.30000000' DAY_SECOND) | +----------------------------------------------------------------------------+ | NULL | +----------------------------------------------------------------------------+ returns NULL. It could parse 3000000 as fractional second digits (notice DAY_SECOND instead of DAY_MICROSECOND). But this is a separate problem.

Alexander Barkov added a comment - 2018-02-08 05:42 The above query mentioned by Lukas returns this result: date_add('1000-01-01 00:00:00', interval '0.5' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.50' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.500' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.5000' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.50000' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.500000' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.5000000' minute_microsecond): 1000-01-01 00:00:05.000000 interval '0.5000000' minute_microsecond should be fixed to treat it as '0.5' seconds, like all other examples in this query do The manual should be updated