mysql时间延迟表达式,MYSQL每日一学 - 时间间隔表达式

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'