MariaDB Composite Date & Time Units Explained

MariaDB includes a bunch of date and time units that you can use when working with date and time values. For example, MONTH is a unit, and HOUR is another unit.

Some units are composite units. Composite units are when two units get combined into one. The naming convention is that each unit name is separated by an underscore. For example, MINUTE_SECOND is for minutes and seconds.

Below are some examples that demonstrate how composite units work in MariaDB.

List of Composite Units

First, here is a list of the composite units available in MariaDB:

Unit	Description
`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

Just looking at their names provides a pretty good clue as to what they do.

Composite units (as with any date/time unit) can be used when extracting parts of a date/time value, and also when doing things like adding and subtracting a time interval to a date/time value.

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.

Example – Extracting Composite Units

Date and time units can be used with a bunch of different functions and in various other contexts. One of the functions that accept these units is the EXTRACT() function. This function returns a specified date/time unit from a given date or datetime value.

Here’s an example of using a composite unit to extract the year and month from a date:

SELECT EXTRACT(YEAR_MONTH FROM '2030-12-25');

Result:

+---------------------------------------+
| EXTRACT(YEAR_MONTH FROM '2030-12-25') |
+---------------------------------------+
|                                203012 |
+---------------------------------------+

We can see that the year and month are returned as one value.

Example – Adding/Subtracting Composite Units

Composite units can also be used to add and subtract date and time intervals.

Example:

SELECT '2030-12-25' + INTERVAL '2:08' DAY_HOUR;

Result:

+-----------------------------------------+
| '2030-12-25' + INTERVAL '2:08' DAY_HOUR |
+-----------------------------------------+
| 2030-12-27 08:00:00                     |
+-----------------------------------------+

In this case, there are two parts to be mindful of with regards to the composite unit.

As in the previous example, we used a composite unit (in this case DAY_HOUR). With regards to the actual interval to add, we used a colon to separate each side of the composite unit.

In this case we specified '2:08', which added 2 days and 8 hours to the date value. The original date doesn’t have a time portion, and so it’s assumed that the initial time is 00:00:00.

MariaDB is reasonably forgiving with the strictness of the format. We can get the same effect by using different separators, and also by omitting the leading zero.

Here’s another example that uses the DATE_ADD() function with various time intervals:

SELECT DATE_ADD('2030-12-25', INTERVAL '2!8' DAY_HOUR);

Result:

+-------------------------------------------------+
| DATE_ADD('2030-12-25', INTERVAL '2!8' DAY_HOUR) |
+-------------------------------------------------+
| 2030-12-27 08:00:00                             |
+-------------------------------------------------+

Here are some more examples of various 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' 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-01 11:30:00 |
+---------------------+---------------------+---------------------+