How to Subtract Hours from a Datetime Value in MariaDB

by

Here are some options for subtracting one or more hours from a datetime value 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', '01:00:00');

Result:

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

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

Example:

SELECT DATE_SUB('2021-05-01 10:00:00', INTERVAL 1 HOUR);

Result:

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

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

Example:

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

Result:

+---------------------------------------------------------------+
| DATE_SUB('2021-05-01 10:00:00', INTERVAL '15:30' HOUR_MINUTE) |
+---------------------------------------------------------------+
| 2021-04-30 18:30:00                                           |
+---------------------------------------------------------------+

Passing Just the Date

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

SELECT DATE_SUB('2021-05-01', INTERVAL 1 HOUR);

Result:

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

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

Multiple Hours

If you need to subtract more than one hour, just use that number instead of 1. However, the HOUR keyword remains non-plural regardless:

SELECT DATE_SUB('2021-05-01 10:00:00', INTERVAL 2 HOUR);

Result:

+--------------------------------------------------+
| DATE_SUB('2021-05-01 10:00:00', INTERVAL 2 HOUR) |
+--------------------------------------------------+
| 2021-05-01 08:00:00                              |
+--------------------------------------------------+

The Subtraction Operator (-)

Another way to subtract hours from a datetime expression is to use the subtraction operator (-), also known as the minus operator.

Example:

SELECT '2021-05-01 10:00:00' - INTERVAL 48 HOUR;

Result:

+------------------------------------------+
| '2021-05-01 10:00:00' - INTERVAL 48 HOUR |
+------------------------------------------+
| 2021-04-29 10:00: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 -48 HOUR;

Result:

+-------------------------------------------+
| '2021-05-01 10:00:00' + INTERVAL -48 HOUR |
+-------------------------------------------+
| 2021-04-29 10:00:00                       |
+-------------------------------------------+

Other Ways to Subtract Hours

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