MariaDB Date and Time Units

Below is a full list of the date and time units that can be used in MariaDB.

Unit	Description
`MICROSECOND`	Microseconds
`SECOND`	Seconds
`MINUTE`	Minutes
`HOUR`	Hours
`DAY`	Days
`WEEK`	Weeks
`MONTH`	Months
`QUARTER`	Quarters
`YEAR`	Years
`SECOND_MICROSECOND`	Seconds.Microseconds
`MINUTE_MICROSECOND`	Minutes.Seconds.Microseconds
`MINUTE_SECOND`	Minutes.Seconds
`HOUR_MICROSECOND`	Hours.Minutes.Seconds.Microseconds
`HOUR_SECOND`	Hours.Minutes.Seconds
`HOUR_MINUTE`	Hours.Minutes
`DAY_MICROSECOND`	Days Hours.Minutes.Seconds.Microseconds
`DAY_SECOND`	Days Hours.Minutes.Seconds
`DAY_MINUTE`	Days Hours.Minutes
`DAY_HOUR`	Days Hours
`YEAR_MONTH`	Years-Months

These can be used with the + and - operators when performing arithmetic on dates, with functions such as ADDDATE(), SUBDATE(), DATE_ADD(), DATE_SUB(), EXTRACT(), TIMESTAMPADD(), and TIMESTAMPDIFF().

They can also be used in the ON SCHEDULE clause of the CREATE_EVENT() and ALTER_EVENT() functions.

The time units containing an underscore are composite units. These consist of more than one base time unit. These are kind of a shorthand way to specify multiple units in one go. Each unit can be separated by any punctuation character.

Examples

Here’s an example of adding an hour to a datetime expression:

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

Result:

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

In this case we use the + operator to perform the addition.

Datetime Functions

The date and time units can be used with various date functions.

Here it is with the DATE_ADD() function:

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

Result:

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

Composite Units

Here’s an example that uses composite units:

SELECT 
    DATE_ADD('2021-05-01 10:00:00', INTERVAL '1:2' YEAR_MONTH) AS "YEAR_MONTH",
    DATE_ADD('2021-05-01 10:00:00', INTERVAL '1:25:35' HOUR_SECOND) AS "HOUR_SECOND",
    DATE_ADD('2021-05-01 10:00:00', INTERVAL '1:30:25' DAY_MINUTE) AS "DAY_MINUTE";

Result:

+---------------------+---------------------+---------------------+
| YEAR_MONTH          | HOUR_SECOND         | DAY_MINUTE          |
+---------------------+---------------------+---------------------+
| 2022-07-01 10:00:00 | 2021-05-01 11:25:35 | 2021-05-03 16:25:00 |
+---------------------+---------------------+---------------------+

The integer numbers can be separated by any punctuation character. Therefore, we could replace the colons with periods to get the same outcome:

SELECT 
    DATE_ADD('2021-05-01 10:00:00', INTERVAL '1.2' YEAR_MONTH) AS "YEAR_MONTH",
    DATE_ADD('2021-05-01 10:00:00', INTERVAL '1.25.35' HOUR_SECOND) AS "HOUR_SECOND",
    DATE_ADD('2021-05-01 10:00:00', INTERVAL '1.30.25' DAY_MINUTE) AS "DAY_MINUTE";

Result:

+---------------------+---------------------+---------------------+
| YEAR_MONTH          | HOUR_SECOND         | DAY_MINUTE          |
+---------------------+---------------------+---------------------+
| 2022-07-01 10:00:00 | 2021-05-01 11:25:35 | 2021-05-03 16:25:00 |
+---------------------+---------------------+---------------------+