MySQL时间加减函数DATE_ADD和DATE_SUB

语法

-- 添加时间间隔 
DATE_ADD(date, INTERVAL expr type)

-- 减少时间间隔 
DATE_SUB(date, INTERVAL expr type)      

1、说明

date 日期表达式

expr 时间间隔

type 类型

2、type 常用值

MICROSECOND 
SECOND 
MINUTE 
HOUR
DAY 
WEEK 
MONTH 
QUARTER 
YEAR      

3、示例：当前时间增加一天

mysql> select now(), date_add(now(), interval 1 day);
+---------------------+---------------------------------+
| now()               | date_add(now(), interval 1 day) |
+---------------------+---------------------------------+
| 2019-10-31 21:57:53 | 2019-11-01 21:57:53             |
+---------------------+---------------------------------+      

实例操作

更新数据库中存在的时间字段

-- 建表
create table student(
    id int(11) PRIMARY key not null auto_increment,
    name varchar(10),
    borthday datetime
)

-- 插入数据
insert into student(name, borthday) 
values ("小红", "2001-01-01 01:01:01")

-- 查看数据
select * from student ;
+----+--------+---------------------+
| id | name   | borthday            |
+----+--------+---------------------+
|  1 | 小红   | 2001-01-01 01:01:01 |
+----+--------+---------------------+

-- 增加一年时间
UPDATE student 
SET borthday = DATE_ADD(borthday, INTERVAL 1 YEAR);

select *  from student;
+----+--------+---------------------+
| id | name   | borthday            |
+----+--------+---------------------+
|  1 | 小红   | 2002-01-01 01:01:01 |
+----+--------+---------------------+

-- 减少一个月时间
UPDATE student 
SET borthday = DATE_SUB(borthday, INTERVAL 1 MONTH);

select *  from student;
+----+--------+---------------------+
| id | name   | borthday            |
+----+--------+---------------------+
|  1 | 小红   | 2001-12-01 01:01:01 |
+----+--------+---------------------+
      

参考

Mysql 更新时间（加上或者减去一段时间）