8 Ways to Add an Hour to a Datetime in MariaDB

Posted on by Ian

There are many ways to perform date arithmetic in MariaDB. This includes adding or subtracting a certain number of a given date part from a date or datetime value.

In this article, I present 8 ways to add an hour to a datetime value in MariaDB.

The DATE_ADD() Function

The DATE_ADD() function allows you to add an amount to a date or datetime expression. This includes adding time parts, such as the hour.

Example:

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

Result:

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

In this case, I added one hour to the datetime expression.

Here’s what happens if I provide just the date:

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

Result:

+-----------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 1 HOUR) |
+-----------------------------------------+
| 2021-05-01 01:00:00                     |
+-----------------------------------------+

The time part is appended to the result, and it is assumed that the initial time was 00:00:00.

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

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

Result:

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

It’s also possible to use another date/time unit with the appropriate number of that unit. Therefore you could specify a certain number of minutes, such as 60 or 120.

Example:

SELECT DATE_ADD('2021-05-01 10:00:00', INTERVAL 60 MINUTE);

Result:

+-----------------------------------------------------+
| DATE_ADD('2021-05-01 10:00:00', INTERVAL 60 MINUTE) |
+-----------------------------------------------------+
| 2021-05-01 11:00:00                                 |
+-----------------------------------------------------+

You can also use a composite date/time unit to add multiple units. For example, if you wanted to add 1 hour and 30 minutes, you could do this:

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

Result:

+--------------------------------------------------------------+
| DATE_ADD('2021-05-01 10:00:00', INTERVAL '1:30' HOUR_MINUTE) |
+--------------------------------------------------------------+
| 2021-05-01 11:30:00                                          |
+--------------------------------------------------------------+

See MariaDB Date and Time Units for a full list of date/time units.

The ADDDATE() Function

Another way to add an hour to a datetime expression is to use the ADDDATE() function. This function has two syntaxes:

  1. One syntax allows you to add a number of days to a date, which is not what we’re doing here (unless we plan to add 24 hours).
  2. The other syntax is the same as with the DATE_ADD() function above. When using this syntax, ADDDATE() is a synonym for DATE_ADD().

Example:

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

Result:

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

The DATE_SUB() Function

The DATE_SUB() function allows you to subtract an amount to a date. However, if you provide a negative value to subtract, then it ends up being added to the date.

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 11:00:00                               |
+---------------------------------------------------+

The SUBDATE() Function

The SUBDATE() function is a synonym for DATE_SUB() when using the following syntax.

Example:

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

Result:

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

The + Operator

Another option is to use the + operator.

The + operator is used to perform addition, and we can use it on dates, along with the applicable date/time unit to add a certain number of hours to our date.

Example:

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

Result:

+-----------------------------------------+
| '2021-05-01 10:00:00' + INTERVAL 1 HOUR |
+-----------------------------------------+
| 2021-05-01 11:00:00                     |
+-----------------------------------------+

The - Operator

The - operator can be used to subtract an amount from a date. But if it’s used to subtract a negative number, then the result is a positive amount being added to the date:

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

Result:

+------------------------------------------+
| '2021-05-01 10:00:00' - INTERVAL -1 HOUR |
+------------------------------------------+
| 2021-05-01 11:00:00                      |
+------------------------------------------+

The ADDTIME() Function

You can use the ADDTIME() function to add a number of hours to a datetime expression.

Example:

SELECT ADDTIME('2021-05-01 10:30:45', '01:0:0');

Result:

+------------------------------------------+
| ADDTIME('2021-05-01 10:30:45', '01:0:0') |
+------------------------------------------+
| 2021-05-01 11:30:45                      |
+------------------------------------------+

One benefit of this function is that you can also change the other time units, such as minutes, seconds, etc.

The SUBTIME() Function

You can alternatively switch it around and use SUBTIME() with a negative value if you so wish:

SELECT SUBTIME('2021-05-01 10:30:45', '-01:0:0');

Result:

+-------------------------------------------+
| SUBTIME('2021-05-01 10:30:45', '-01:0:0') |
+-------------------------------------------+
| 2021-05-01 11:30:45                       |
+-------------------------------------------+

Other Date and Time Units

The above examples add a number of hours to the datetime value, but we can use the same techniques to add any date/time unit. See MariaDB Date and Time Units for a full list of date/time units that can be used with the above functions and operators.