Here are some options for subtracting one or more minutes 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:30:00');

Result:

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

You can also change the other time units, such as hours, 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 minutes.

Example:

SELECT DATE_SUB('2021-05-01 10:00:00', INTERVAL 30 MINUTE);

Result:

+-----------------------------------------------------+
| DATE_SUB('2021-05-01 10:00:00', INTERVAL 30 MINUTE) |
+-----------------------------------------------------+
| 2021-05-01 09:30:00                                 |
+-----------------------------------------------------+

Note that the MINUTE keyword remains non-plural regardless of whether we’re subtracting one minute or more.

You can also use composite date and time units. For example, you could do minutes and seconds all in one go.

Example:

SELECT DATE_SUB('2021-05-01 10:00:00', INTERVAL '15:30' MINUTE_SECOND);

Result:

+-----------------------------------------------------------------+
| DATE_SUB('2021-05-01 10:00:00', INTERVAL '15:30' MINUTE_SECOND) |
+-----------------------------------------------------------------+
| 2021-05-01 09:44:30                                             |
+-----------------------------------------------------------------+

Passing Just the Date

Here’s what happens if we provide just a date value to the DATE_SUB() function:

SELECT DATE_SUB('2021-05-01', INTERVAL 30 MINUTE);

Result:

+--------------------------------------------+
| DATE_SUB('2021-05-01', INTERVAL 30 MINUTE) |
+--------------------------------------------+
| 2021-04-30 23:30:00                        |
+--------------------------------------------+

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

The Subtraction Operator (-)

Another way to subtract one or more minutes 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 MINUTE;

Result:

+--------------------------------------------+
| '2021-05-01 10:00:00' - INTERVAL 30 MINUTE |
+--------------------------------------------+
| 2021-05-01 09:30:00                        |
+--------------------------------------------+

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 MINUTE;

Result:

+---------------------------------------------+
| '2021-05-01 10:00:00' + INTERVAL -30 MINUTE |
+---------------------------------------------+
| 2021-05-01 09:30:00                         |
+---------------------------------------------+

Other Ways to Subtract Minutes

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

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