MySQL时间加减函数DATE

语法

-- 添加时间间隔 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 更新时间（加上或者减去一段时间）