Subtract Microseconds from a Datetime Value in MariaDB

by

Here are some options for subtracting one or more microseconds from a datetime expression in MariaDB.

The SUBTIME() Function

The SUBTIME() subtracts a given amount of time from a time or datetime value.

Example:

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 09:59:59.876544                        |
+---------------------------------------------------+

You can also change the other time units, such as hours, minutes, seconds, etc.

The DATE_SUB() Function

The DATE_SUB() function allows you to subtract a certain number of date/time units from a date or datetime expression. Therefore we can use this function to return the datetime value, minus a certain number of microseconds.

Example:

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

Result:

+-----------------------------------------------------------+
| DATE_SUB('2021-05-01 10:00:00', INTERVAL 300 MICROSECOND) |
+-----------------------------------------------------------+
| 2021-05-01 09:59:59.999700                                |
+-----------------------------------------------------------+

Note that the MICROSECOND keyword remains non-plural regardless of whether you subtract one microsecond or more.

Passing Just the Date

Here’s what happens if we provide just a date value:

SELECT DATE_SUB('2021-05-01', INTERVAL 3 MICROSECOND);

Result:

+------------------------------------------------+
| DATE_SUB('2021-05-01', INTERVAL 3 MICROSECOND) |
+------------------------------------------------+
| 2021-04-30 23:59:59.999997                     |
+------------------------------------------------+

It assumes the time starts at 00:00:00, and so the MICROSECOND amount is subtracted from that.

The Subtraction Operator (-)

Another way to subtract one or more microseconds from a datetime value is to use the subtraction operator (-), also known as the minus operator.

Example:

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

Result:

+-------------------------------------------------+
| '2021-05-01 10:00:00' - INTERVAL 30 MICROSECOND |
+-------------------------------------------------+
| 2021-05-01 09:59:59.999970                      |
+-------------------------------------------------+

The Addition Operator (+)

You can alternatively use the addition operator (+) along with a negative amount.

Example:

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

Result:

+--------------------------------------------------+
| '2021-05-01 10:00:00' + INTERVAL -30 MICROSECOND |
+--------------------------------------------------+
| 2021-05-01 09:59:59.999970                       |
+--------------------------------------------------+

Other Ways to Subtract Microseconds

Here are some other approaches you could use to subtract one or more microseconds from a datetime value:

  • The SUBTIME() function.
  • The ADDTIME() function (providing a negative amount will subtract that amount from the datetime value).
  • The SUBDATE() function (this is a synonym for DATE_SUB() when used with the same syntax).
  • The DATE_ADD() function (providing a negative amount will subtract that amount from the datetime value).
  • The ADDDATE() function (providing a negative amount will subtract that amount from the datetime value).