8 Ways to Add Minutes to a Datetime in MariaDB

Posted on by Ian

MariaDB provides many ways to perform date arithmetic. 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 one or more minutes 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 minutes.

Example:

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

Result:

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

In this case, I added 35 minutes to the datetime expression. Note that the MINUTE keyword remains non-plural regardless of whether I’m adding one minute or more.

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

SELECT DATE_ADD('2021-05-01', INTERVAL 35 MINUTE);

Result:

+--------------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 35 MINUTE) |
+--------------------------------------------+
| 2021-05-01 00:35:00                        |
+--------------------------------------------+

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

When adding 60 minutes, we can use the HOUR keyword if we prefer:

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                              |
+--------------------------------------------------+

Obviously, this technique works with 120 minutes, 180, and so on. Just use the applicable number of hours.

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 minutes 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 minutes in blocks of 1,440, which is how many minutes are in a day).
  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 35 MINUTE);

Result:

+----------------------------------------------------+
| ADDDATE('2021-05-01 10:00:00', INTERVAL 35 MINUTE) |
+----------------------------------------------------+
| 2021-05-01 10:35:00                                |
+----------------------------------------------------+

The DATE_SUB() Function

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

Example:

SELECT DATE_SUB('2021-05-01 10:00:00', INTERVAL -35 MINUTE);

Result:

+------------------------------------------------------+
| DATE_SUB('2021-05-01 10:00:00', INTERVAL -35 MINUTE) |
+------------------------------------------------------+
| 2021-05-01 10:35: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 -35 MINUTE);

Result:

+-----------------------------------------------------+
| SUBDATE('2021-05-01 10:00:00', INTERVAL -35 MINUTE) |
+-----------------------------------------------------+
| 2021-05-01 10:35: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 minutes to our date.

Example:

SELECT '2021-05-01 10:00:00' + INTERVAL 35 MINUTE;

Result:

+--------------------------------------------+
| '2021-05-01 10:00:00' + INTERVAL 35 MINUTE |
+--------------------------------------------+
| 2021-05-01 10:35: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 -35 MINUTE;

Result:

+---------------------------------------------+
| '2021-05-01 10:00:00' - INTERVAL -35 MINUTE |
+---------------------------------------------+
| 2021-05-01 10:35:00                         |
+---------------------------------------------+

The ADDTIME() Function

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

Example:

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

Result:

+--------------------------------------------+
| ADDTIME('2021-05-01 10:00:00', '00:30:00') |
+--------------------------------------------+
| 2021-05-01 10:30:00                        |
+--------------------------------------------+

One benefit of this function is that you can also change the other time units, such as hours, 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:00:00', '-00:30:00');

Result:

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

Other Date and Time Units

The above examples add a number of minutes 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.