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

Result:

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

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

Example:

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

Result:

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

Note that the SECOND keyword remains non-plural regardless of whether you’re subtracting one second 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:

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

Result:

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

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

The Subtraction Operator (-)

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

Result:

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

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

Result:

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

Other Ways to Subtract Seconds

Here are some other approaches you could use to subtract one or more seconds 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).