Subtract hours from a datetime value in MariaDB

If you need to subtract 1 or several hours from a MariaDB datetime, you can use the following functions:

• SUBTIME()
• ADDTIME()
• SUBDATE()
• ADDDATE()
• DATE_SUB()
• DATE_ADD()

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 2 hours from a given datetime value:

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

Output:

+--------------------------------------------+
| SUBTIME('2021-12-12 12:00:00', '02:00:00') |
+--------------------------------------------+
| 2021-12-12 10:00:00                        |
+--------------------------------------------+

The following statement uses the SUBTIME() function to subtract 2 hours and 30 minutes from a given time value:

SELECT SUBTIME('12:00:00', '02:30:00');

Output:

+---------------------------------+
| SUBTIME('12:00:00', '02:30:00') |
+---------------------------------+
| 09: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 2 hours from a given datetime value:

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

Output:

+---------------------------------------------+
| ADDTIME('2021-12-12 12:00:00', '-02:00:00') |
+---------------------------------------------+
| 2021-12-12 10:00:00                         |
+---------------------------------------------+

The following statement uses the ADDTIME() function to subtract 2 hours and 30 minutes from a given time value:

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

Output:

+----------------------------------+
| ADDTIME('12:00:00', '-02:30:00') |
+----------------------------------+
| 09: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 2 hours from a given datetime value:

SELECT SUBDATE('2021-12-12 12:00:00', INTERVAL 2 HOUR);

Output:

+-------------------------------------------------+
| SUBDATE('2021-12-12 12:00:00', INTERVAL 2 HOUR) |
+-------------------------------------------------+
| 2021-12-12 10:00: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 2 hours from a given datetime value:

SELECT ADDDATE('2021-12-12 12:00:00', INTERVAL -2 HOUR);

Output:

+--------------------------------------------------+
| ADDDATE('2021-12-12 12:00:00', INTERVAL -2 HOUR) |
+--------------------------------------------------+
| 2021-12-12 10:00:00                              |
+--------------------------------------------------+

In this case, we pass in INTERVAL -2 HOUR for 2 hours, 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 2 hours from a given datetime value:

SELECT DATE_SUB('2021-12-12 12:00:00', INTERVAL 2 HOUR);

Output:

+--------------------------------------------------+
| DATE_SUB('2021-12-12 12:00:00', INTERVAL 2 HOUR) |
+--------------------------------------------------+
| 2021-12-12 10:00: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 2 hours from a given datetime value:

SELECT DATE_ADD('2021-12-12 12:00:00', INTERVAL -2 HOUR);

Output:

+---------------------------------------------------+
| DATE_ADD('2021-12-12 12:00:00', INTERVAL -2 HOUR) |
+---------------------------------------------------+
| 2021-12-12 10:00:00                               |
+---------------------------------------------------+

In this case, we pass in INTERVAL -2 HOUR for 2 hours, which is a negative number.

Example 7: Addition and subtraction operators

MariaDB allows you to perform calculations on -.

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

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

Output:

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

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

The following statement subtracts one hour from '2022-12-13 12:00:00':

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

Output:

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

Conclusion

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