> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date-and-time-units.md).

# Date and Time Units

The `INTERVAL` keyword can be used to add or subtract a time interval of time to a [DATETIME](/docs/server/reference/data-types/date-and-time-data-types/datetime.md), [DATE](/docs/server/reference/data-types/date-and-time-data-types/date.md) or [TIME](/docs/server/reference/data-types/date-and-time-data-types/time.md) value.

The syntax is:

```sql
INTERVAL time_quantity time_unit
```

For example, the `SECOND` unit is used below by the [DATE\_ADD()](/docs/server/reference/sql-functions/date-time-functions/date_add.md) function:

```sql
SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00                       |
+-------------------------------------------+
```

The following units are valid:

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

The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, `time_quantity` is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character.

Example of composite units:

```sql
INTERVAL '2:2' YEAR_MONTH
INTERVAL '1:30:30' HOUR_SECOND
INTERVAL '1!30!30' HOUR_SECOND -- same as above
```

Time units can be used in the following contexts:

* after a [+](/docs/server/reference/sql-structure/operators/arithmetic-operators/addition-operator.md) or a [-](/docs/server/reference/sql-statements/data-manipulation/selecting-data/set-operations/minus.md) operator;
* with the following `DATE` or `TIME` functions: [ADDDATE()](/docs/server/reference/sql-functions/date-time-functions/adddate.md), [SUBDATE()](/docs/server/reference/sql-functions/date-time-functions/subdate.md), [DATE\_ADD()](/docs/server/reference/sql-functions/date-time-functions/date_add.md), [DATE\_SUB()](/docs/server/reference/sql-functions/date-time-functions/date_sub.md), [TIMESTAMPADD()](/docs/server/reference/sql-functions/date-time-functions/timestampadd.md), [TIMESTAMPDIFF()](/docs/server/reference/sql-functions/date-time-functions/timestampdiff.md), [EXTRACT()](/docs/server/reference/sql-functions/date-time-functions/extract.md);
* in the `ON SCHEDULE` clause of [CREATE EVENT](/docs/server/reference/sql-statements/data-definition/create/create-event.md) and [ALTER EVENT](/docs/server/server-usage/triggers-events/event-scheduler/alter-event.md);
* when defining a [partitioning](/docs/server/server-usage/tables/create-table.md#partitions) `BY SYSTEM_TIME` .

## See Also

* [Date and time literals](/docs/server/reference/sql-structure/sql-language-structure/date-and-time-literals.md)
* [Operator Precedence](/docs/server/reference/sql-structure/operators/operator-precedence.md)

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}