An interval expression is used to represent and calculate time intervals.
In MySQL-compatible mode, interval expressions are typically used in combination with date and time expressions. The syntax is as follows:
INTERVAL expr date_unit
Common values of date_unit include:
DAY、WEEK、MONTH、QUARTER、YEARHOUR、MINUTE、SECOND、MICROSECOND- Composite units:
DAY_HOUR,DAY_MINUTE,DAY_second,HOUR_MINUTE,HOUR_SECOND,YEAR_MONTH, and so on.
For a complete list of date_unit, see the date_unit production rule in Expression syntax.
Usage instructions
- Interval expressions are typically used with the
+or-operator to perform addition and subtraction on date and time values. expr: an integer value indicating the number of time intervals.
Examples
Add 7 days to the current date:
obclient> SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
+--------------------------------+
| DATE_ADD(NOW(), INTERVAL 7 DAY) |
+--------------------------------+
| 2026-06-09 17:13:00 |
+--------------------------------+
1 row in set
Calculate the interval between two dates:
obclient> SELECT TIMESTAMPDIFF(MONTH, '2026-01-01', '2026-06-01') AS month_diff;
+------------+
| month_diff |
+------------+
| 5 |
+------------+
1 row in set