1. 获取:
select now(),CURDATE(),CURTIME(),CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP(),
DATE_FORMAT(now(),'%Y%m%d')
2. 日期加减常量(年,月,日等)
select DATE_ADD(NOW(),INTERVAL 1 year), -- DATE_ADD(date,INTERVAL expr unit)
ADDDATE(now(),INTERVAL 2 day), -- ADDDATE(date,INTERVAL expr unit),默认单位为day
TIMESTAMPADD(day,3,NOW()), -- TIMESTAMPADD(unit,interval,datetime_expr)
SUBDATE(now(),INTERVAL 3 month), -- SUBDATE(date,INTERVAL expr unit),
DATE_SUB(now(),INTERVAL 4 week) -- DATE_SUB(date,INTERVAL expr unit)
unit:year:年,quarter:季,month:月,week:周,day:天,hour:小时,minuter:分钟,second:秒,microsecond:毫秒;
add相关函数expr用负数,对应减法; sub相关函数expr用负数对应加法;
3. 日期之间计算
set @his = DATE_sub(NOW(), INTERVAL 365 DAY) ;
select timediff('2022-07-22 12:30:00', '2022-07-12 12:29:30'),
@his,
TIMESTAMPDIFF(YEAR,@his,NOW()), -- TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
DATEDIFF(@his,NOW()), -- DATEDIFF(expr1,expr2)
datediff('20220712','20220701'),
TO_DAYS(@his)-TO_DAYS(NOW())