MariaDB Date and Time Units

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

UnitDescription
MICROSECONDMicroseconds
SECONDSeconds
MINUTEMinutes
HOURHours
DAYDays
WEEKWeeks
MONTHMonths
QUARTERQuarters
YEARYears
SECOND_MICROSECONDSeconds.Microseconds
MINUTE_MICROSECONDMinutes.Seconds.Microseconds
MINUTE_SECONDMinutes.Seconds
HOUR_MICROSECONDHours.Minutes.Seconds.Microseconds
HOUR_SECONDHours.Minutes.Seconds
HOUR_MINUTEHours.Minutes
DAY_MICROSECONDDays Hours.Minutes.Seconds.Microseconds
DAY_SECONDDays Hours.Minutes.Seconds
DAY_MINUTEDays Hours.Minutes
DAY_HOURDays Hours
YEAR_MONTHYears-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 |
+---------------------+---------------------+---------------------+