|
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.
|
|
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...
|
|
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.
|
|
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
|