语法
-- 添加时间间隔 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 | +----+--------+---------------------+
参考
