Date & Time Units in MySQL (Full List)

Here’s a list of units that can be used in MySQL datetime and interval functions.

unit ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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 |
+---------------------+---------------------+---------------------+