Subtract minutes from a datetime value in MariaDB

This article implements several examples of subtracting minutes from a MariaDB datetime value.

Posted on

If you need to subtract minutes from a time or datetime value in MariaDB, you can use the following functions:

Alternatively, you can use the addition and subtraction operators directly.

The following are a few actual cases.

Example 1: SUBTIME()

SUBTIME() is used to subtract a given amount of time from a given time or datetime value. The following statement uses the SUBTIME() function to subtract 30 minutes from a given datetime value:

SELECT SUBTIME('2021-12-12 12:00:00', '00:30:00');

Output:

+--------------------------------------------+
| SUBTIME('2021-12-12 12:00:00', '00:30:00') |
+--------------------------------------------+
| 2021-12-12 11:30:00                        |
+--------------------------------------------+

Example 2: ADDTIME()

ADDTIME() is used to add a given amount of time from a given time or datetime value. The following statement uses the ADDTIME() function to subtract 30 minutes from a given datetime value:

SELECT ADDTIME('2021-12-12 12:00:00', '-00:30:00');

Output:

+---------------------------------------------+
| ADDTIME('2021-12-12 12:00:00', '-00:30:00') |
+---------------------------------------------+
| 2021-12-12 11:30:00                         |
+---------------------------------------------+

Example 3: SUBDATE()

SUBDATE() is used to subtract a given amount of time from a given date or datetime value. The following statement uses the SUBDATE() function to subtract 30 minutes from a given datetime value:

SELECT SUBDATE('2021-12-12 12:00:00', INTERVAL 30 MINUTE);

Output:

+----------------------------------------------------+
| SUBDATE('2021-12-12 12:00:00', INTERVAL 30 MINUTE) |
+----------------------------------------------------+
| 2021-12-12 11:30:00                                |
+----------------------------------------------------+

Example 4: ADDDATE()

ADDDATE() is used to add a given amount of time to a given date or datetime value. The following statement uses the ADDDATE() function to subtract 30 minutes from a given datetime value:

SELECT ADDDATE('2021-12-12 12:00:00', INTERVAL -30 MINUTE);

Output:

+-----------------------------------------------------+
| ADDDATE('2021-12-12 12:00:00', INTERVAL -30 MINUTE) |
+-----------------------------------------------------+
| 2021-12-12 11:30:00                                 |
+-----------------------------------------------------+

In this case, we pass in INTERVAL -30 MINUTE for 30 minutes, which is a negative number.

Example 5: DATE_SUB()

DATE_SUB() is used to subtract a given amount of time from a given date or datetime value. The following statement uses the DATE_SUB() function to subtract 30 minutes from a given datetime value:

SELECT DATE_SUB('2021-12-12 12:00:00', INTERVAL 30 MINUTE);

Output:

+-----------------------------------------------------+
| DATE_SUB('2021-12-12 12:00:00', INTERVAL 30 MINUTE) |
+-----------------------------------------------------+
| 2021-12-12 11:30:00                                 |
+-----------------------------------------------------+

Example 6: DATE_ADD()

DATE_ADD() is used to add a given amount of time to a given date or datetime value. The following statement uses the DATE_ADD() function to subtract 30 minutes from a given datetime value:

SELECT DATE_ADD('2021-12-12 12:00:00', INTERVAL -30 MINUTE);

Output:

+------------------------------------------------------+
| DATE_ADD('2021-12-12 12:00:00', INTERVAL -30 MINUTE) |
+------------------------------------------------------+
| 2021-12-12 11:30:00                                  |
+------------------------------------------------------+

In this case, we pass in INTERVAL -30 MINUTE for 30 minutes, which is a negative number.

Example 7: Addition and subtraction operators

The following statement uses subtraction - to subtract 1 minute from '2022-12-13 12:00:00':

SELECT '2022-12-13 12:00:00' - INTERVAL 1 MINUTE;

Output:

+-------------------------------------------+
| '2022-12-13 12:00:00' - INTERVAL 1 MINUTE |
+-------------------------------------------+
| 2022-12-13 11:59:00                       |
+-------------------------------------------+

Of course, you can also use the addition operator. And just change the INTERVAL value to a negative number.

The following statement subtracts 1 minute from '2022-12-13 12:00:00':

SELECT '2022-12-13 12:00:00' + INTERVAL -1 MINUTE;

Output:

+--------------------------------------------+
| '2022-12-13 12:00:00' + INTERVAL -1 MINUTE |
+--------------------------------------------+
| 2022-12-13 11:59:00                        |
+--------------------------------------------+

Conclusion

This article demonstrates several examples for subtracting a specified number of minutes from a specified time or datetime value.