DATE_ADD()
定义
DATE_ADD() 函数向日期添加指定的时间间隔。
语法DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值:MICROSECOND,SECOND,MINUTE,HOUR,DAY,WEEK,MONTH,QUARTER,YEAR,SECOND_MICROSECOND,MINUTE_MICROSECOND,MINUTE_SECOND,HOUR_MICROSECOND,HOUR_SECOND,HOUR_MINUTE,DAY_MICROSECOND,DAY_SECOND,DAY_MINUTE,DAY_HOUR,YEAR_MONTH
例子:mysql> select now() as now ,date_add(now(), interval -40 second) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-07-12 17:53:36 |
+---------------------+---------------------+
1 row in set (0.01 sec)
mysql> select now() as now ,date_add(now(), interval 40 second) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-07-12 17:54:56 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval -30 minute) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-07-12 17:24:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval 30 minute) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-07-12 18:24:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval -2 hour) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-07-12 15:54:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval 2 hour) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-07-12 19:54:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval 1 day) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-07-13 17:54:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval -1 day) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-07-11 17:54:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval 1 month) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-08-12 17:54:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval -1 month) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:16 | 2019-06-12 17:54:16 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> select now() as now ,date_add(now(), interval 1 year) as date;
+---------------------+---------------------+
| now | date |
+---------------------+---------------------+
| 2019-07-12 17:54:18 | 2020-07-12 17:54:18 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql>
DATE_SUB()
定义
DATE_SUB() 函数从日期减去指定的时间间隔。
语法DATE_SUB(date,INTERVAL expr type)
参数与用法同DATE_ADD()
DATEDIFF()
定义
DATEDIFF() 函数返回两个日期之间的天数。
语法DATEDIFF(date1,date2)
date1 和 date2 参数是合法的日期或日期/时间表达式。
**注释:**只有值的日期部分参与计算。
例子:mysql> select datediff('2008-08-20','2008-08-08') as diff;
+------+
| diff |
+------+
| 12 |
+------+
1 row in set (0.00 sec)
mysql> select datediff(now(),'2008-08-08') as diff;
+------+
| diff |
+------+
| 3990 |
+------+
1 row in set (0.00 sec)
mysql> select datediff('2008-08-08','2008-08-20') as diff;
+------+
| diff |
+------+
| -12 |
+------+
1 row in set (0.00 sec)
mysql> select datediff('2008-08-08',now()) as diff;
+-------+
| diff |
+-------+
| -3990 |
+-------+
1 row in set (0.00 sec)
同样的还有timediff