参考链接:https://dev.mysql.com/doc/refman/5.7/en/expressions.html
Interval表达式(Temporal intervals)的使用
- Interval表达式 在 date_add() 和 date_sub() 函数中的使用
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
-> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
-> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
-> INTERVAL 1 SECOND);
-> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
-> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
-> INTERVAL '1 1:1:1' DAY_SECOND);
-> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
-> INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
-> INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'
- 当传入的时间格式不完整或者错误时interval表达式的返回值示例
mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 DAY);
-> '2023-01-02'
mysql> SELECT DATE_ADD('2023-01-01', INTERVAL 1 HOUR);
-> '2023-01-01 01:00:00'
mysql> SELECT DATE_ADD('2016-07-00', INTERVAL 1 DAY);
-> NULL
mysql> SELECT '2005-03-32' + INTERVAL 1 MONTH;
-> NULL
- interval表达式在select中进行 + 或 - 运算符计算
mysql> SELECT '2018-12-31 23:59:59' + INTERVAL 1 SECOND;
-> '2019-01-01 00:00:00'
mysql> SELECT INTERVAL 1 DAY + '2018-12-31';
-> '2019-01-01'
mysql> SELECT '2025-01-01' - INTERVAL 1 SECOND;
-> '2024-12-31 23:59:59'
附录
时间间隔表达式和单位参数
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' |