If you need to add one or more microseconds to a datetime value in MariaDB, here are 8 options to consider.

The DATE_ADD() Function

The DATE_ADD() function allows you to add an amount to a date or datetime expression. This includes adding time parts, such as hours, minutes, seconds, microseconds, etc.

Example:

SELECT DATE_ADD('2021-05-01 10:00:00', INTERVAL 35 MICROSECOND);

Result:

+----------------------------------------------------------+
| DATE_ADD('2021-05-01 10:00:00', INTERVAL 35 MICROSECOND) |
+----------------------------------------------------------+
| 2021-05-01 10:00:00.000035                               |
+----------------------------------------------------------+

In this case, I added 35 microseconds to the datetime expression. Note that the SECOND keyword remains non-plural regardless of whether I’m adding one second or more.

You might also notice that the microseconds portion wasn’t in my original datetime value, but it was added once I added the microseconds.

Along similar lines, here’s what happens if I provide just the date:

SELECT DATE_ADD('2021-05-01', INTERVAL 35 MICROSECOND);

Result:

+-------------------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 35 MICROSECOND) |
+-------------------------------------------------+
| 2021-05-01 00:00:00.000035                      |
+-------------------------------------------------+

The time part is appended to the result, and it is assumed that the initial time was 00:00:00.000000.

You can also use a composite date/time unit to add multiple units. For example, if you wanted to add 1 minute and 30 seconds, you could do this:

SELECT 
DATE_ADD('2021-05-01 10:00:00', INTERVAL '1:30' SECOND_MICROSECOND);

Result:

+---------------------------------------------------------------------+
| DATE_ADD('2021-05-01 10:00:00', INTERVAL '1:30' SECOND_MICROSECOND) |
+---------------------------------------------------------------------+
| 2021-05-01 10:00:01.300000                                          |
+---------------------------------------------------------------------+

See MariaDB Date and Time Units for a full list of date/time units.

The ADDDATE() Function

Another way to add seconds to a datetime expression is to use the ADDDATE() function. This function has two syntaxes:

1. One syntax allows you to add a number of days to a date, which is not what we’re doing here.
2. The other syntax is the same as with the DATE_ADD() function above. When using this syntax, ADDDATE() is a synonym for DATE_ADD().

Example:

SELECT ADDDATE('2021-05-01 10:00:00', INTERVAL 35 MICROSECOND);

Result:

+---------------------------------------------------------+
| ADDDATE('2021-05-01 10:00:00', INTERVAL 35 MICROSECOND) |
+---------------------------------------------------------+
| 2021-05-01 10:00:00.000035                              |
+---------------------------------------------------------+

The DATE_SUB() Function

The DATE_SUB() function allows you to subtract an amount to a date/datetime expression. However, if you provide a negative value to subtract, then it ends up being added to the date/time.

Example:

SELECT DATE_SUB('2021-05-01 10:00:00', INTERVAL -35 MICROSECOND);

Result:

+-----------------------------------------------------------+
| DATE_SUB('2021-05-01 10:00:00', INTERVAL -35 MICROSECOND) |
+-----------------------------------------------------------+
| 2021-05-01 10:00:00.000035                                |
+-----------------------------------------------------------+

The SUBDATE() Function

The SUBDATE() function is a synonym for DATE_SUB() when using the following syntax.

Example:

SELECT SUBDATE('2021-05-01 10:00:00', INTERVAL -35 MICROSECOND);

Result:

+----------------------------------------------------------+
| SUBDATE('2021-05-01 10:00:00', INTERVAL -35 MICROSECOND) |
+----------------------------------------------------------+
| 2021-05-01 10:00:00.000035                               |
+----------------------------------------------------------+

The + Operator

Another option is to use the + operator.

The + operator is used to perform addition, and we can use it on dates, along with the applicable date/time unit to add a certain number of microseconds to our datetime expression.

Example:

SELECT '2021-05-01 10:00:00' + INTERVAL 35 MICROSECOND;

Result:

+-------------------------------------------------+
| '2021-05-01 10:00:00' + INTERVAL 35 MICROSECOND |
+-------------------------------------------------+
| 2021-05-01 10:00:00.000035                      |
+-------------------------------------------------+

The - Operator

The - operator can be used to subtract an amount from a date. But if it’s used to subtract a negative number, then the result is a positive amount being added to the datetime expression:

SELECT '2021-05-01 10:00:00' - INTERVAL -35 MICROSECOND;

Result:

+--------------------------------------------------+
| '2021-05-01 10:00:00' - INTERVAL -35 MICROSECOND |
+--------------------------------------------------+
| 2021-05-01 10:00:00.000035                       |
+--------------------------------------------------+

The ADDTIME() Function

You can use the ADDTIME() function to add a number of millseconds to a datetime expression.

Example:

SELECT ADDTIME('2021-05-01 10:00:00', '00:00:00.123456');

Result:

+---------------------------------------------------+
| ADDTIME('2021-05-01 10:00:00', '00:00:00.123456') |
+---------------------------------------------------+
| 2021-05-01 10:00:00.123456                        |
+---------------------------------------------------+

One benefit of this function is that you can also change the other time units, such as hours, seconds, etc.

The SUBTIME() Function

You can alternatively switch it around and use SUBTIME() with a negative value if you so wish:

SELECT SUBTIME('2021-05-01 10:00:00', '-00:00:00.123456');

Result:

+----------------------------------------------------+
| SUBTIME('2021-05-01 10:00:00', '-00:00:00.123456') |
+----------------------------------------------------+
| 2021-05-01 10:00:00.123456                         |
+----------------------------------------------------+

Other Date and Time Units

The above examples add a number of microseconds to the datetime value, but we can use the same techniques to add any date/time unit. See MariaDB Date and Time Units for a full list of date/time units that can be used with the above functions and operators.