Mysql 日、周、月、年

1.获取当前日，月，年

-- 日(年月日)
SELECT CURDATE();  
-- 周
SELECT WEEKOFYEAR(CURDATE());
-- 月
SELECT MONTH(CURDATE());
-- 年
SELECT YEAR(CURDATE());
-- 年月日时分秒
SELECT NOW();  
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')
-- 年月
SELECT DATE_FORMAT(NOW(),'%Y-%m')
-- 月日
SELECT DATE_FORMAT(NOW(),'%m-%d')
-- 今天 00:00:00
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d %H:%i:%s');
-- 今天
SELECT CURDATE();
-- 今天 23:59:59
SELECT DATE_SUB(DATE_ADD(CURDATE(),INTERVAL 1 DAY),INTERVAL 1 SECOND);

2.昨天

-- 昨天 00:00:00
SELECT DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 DAY),'%Y-%m-%d %H:%i:%s');
-- 昨天
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);
-- 昨天 23:59:59
SELECT DATE_SUB(CURDATE(),INTERVAL 1 SECOND);

3.明天

-- 明天 00:00:00
SELECT DATE_FORMAT(DATE_ADD(CURDATE(),INTERVAL 1 DAY),'%Y-%m-%d %H:%i:%s');
-- 明天
SELECT DATE_ADD(CURDATE(),INTERVAL 1 DAY);
-- 明天 23:59:59
SELECT DATE_SUB(DATE_ADD(CURDATE(),INTERVAL 2 DAY),INTERVAL 1 SECOND);

4.获取当周的某天

#第一天
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) DAY);
#最后天
select date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 6 DAY);

5.获取当月某天

#  当月第一天
select last_day(curdate());
# 当月最后一天
select DATE_ADD(curdate(),interval -day(curdate())+1 day);
# 获取下个月的第一天
select date_add(curdate()-day(curdate())+1,interval 1 month);

# 获取当月天数
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual;

6.获取当年某天

# 当年第一天
SELECT DATE_SUB(CURDATE(),INTERVAL DAYOFYEAR(NOW()) - 1 DAY);
SELECT concat(YEAR(now()),'-01-01'); 

# 当年最后一天
SELECT concat(YEAR(now()),'-12-31');  

7.时差计算

TIMESTAMPDIFF 计算时间差，可以精确到(YEAR)年、(MONTH)月、(DAY)日、(HOUR)时、(MINUTE)分、(SECOND)秒具体如下
SELECT TIMESTAMPDIFF(YEAR, '2016-09-23 00:00:00', '2021-05-10 00:00:00');
SELECT TIMESTAMPDIFF(MONTH, '2016-09-23 00:00:00', '2021-05-10 00:00:00');
SELECT TIMESTAMPDIFF(DAY, '2016-09-23 00:00:00', '2021-05-10 00:00:00');
SELECT TIMESTAMPDIFF(HOUR, '2016-09-23 00:00:00', '2021-05-10 00:00:00');
SELECT TIMESTAMPDIFF(MINUTE, '2016-09-23 00:00:00', '2021-05-10 00:00:00');
SELECT TIMESTAMPDIFF(SECOND, '2016-09-23 00:00:00', '2021-05-10 00:00:00');

DATEDIFF 计算时差，精确到天
SELECT DATEDIFF('2016-09-23 00:00:00', '2021-05-10 00:00:00');

8.时间格式转换

# str to date
select str_to_date('2023-09-15','%Y-%m-%d');

# date to str
select date_format(now()#,'%Y-%m-%d');

# date to long
Select UNIX_TIMESTAMP(now());

# long to time
select FROM_UNIXTIME(1694740964);