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