Here’s a list of units that can be used in MySQL datetime and interval functions.
unit Value | Expected expr Format |
|---|---|
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 units can be used with the + and - operators when performing arithmetic on dates, with functions such as ADDDATE(), SUBDATE(), DATE_ADD(), DATE_SUB(), and EXTRACT().
They can also be used in the ON SCHEDULE clause of the CREATE_EVENT() and ALTER_EVENT() functions.
The first nine units can be used with TIMESTAMPADD() and TIMESTAMPDIFF() (these two functions don’t support the composite units at the time of writing).
The units containing an underscore are composite units. These consist of more than one base time unit. These can be thought of as 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 a year to a datetime expression:
SELECT '2035-01-01 01:30:45' + INTERVAL 1 YEAR;Result:
2036-01-01 01:30:45
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('2035-01-01 01:30:45', INTERVAL 1 YEAR);Result:
2036-01-01 01:30:45
Composite Units
Here’s an example that uses composite units:
SELECT
DATE_ADD('2035-01-01 01:00:00', INTERVAL '1:2' YEAR_MONTH) AS "YEAR_MONTH",
DATE_ADD('2035-01-01 01:00:00', INTERVAL '1:25:35' HOUR_SECOND) AS "HOUR_SECOND",
DATE_ADD('2035-01-01 01:00:00', INTERVAL '1:30:25' DAY_MINUTE) AS "DAY_MINUTE";Result:
+---------------------+---------------------+---------------------+ | YEAR_MONTH | HOUR_SECOND | DAY_MINUTE | +---------------------+---------------------+---------------------+ | 2036-03-01 01:00:00 | 2035-01-01 02:25:35 | 2035-01-03 07: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('2035-01-01 01:00:00', INTERVAL '1.2' YEAR_MONTH) AS "YEAR_MONTH",
DATE_ADD('2035-01-01 01:00:00', INTERVAL '1.25.35' HOUR_SECOND) AS "HOUR_SECOND",
DATE_ADD('2035-01-01 01:00:00', INTERVAL '1.30.25' DAY_MINUTE) AS "DAY_MINUTE";Result:
+---------------------+---------------------+---------------------+ | YEAR_MONTH | HOUR_SECOND | DAY_MINUTE | +---------------------+---------------------+---------------------+ | 2036-03-01 01:00:00 | 2035-01-01 02:25:35 | 2035-01-03 07:25:00 | +---------------------+---------------------+---------------------+