Mysql生成当月所天数本周所有日期当日24小时时间段

本文主要是介绍Mysql生成当月所天数本周所有日期当日24小时时间段，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

sql直接运行
1、生成当月所有天数

SELECTdate_format(date_add( DATE_ADD( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 0 ) DAY ),'%Y-%m-%d' ) 日期 
FROMmysql.help_topic 
WHEREhelp_topic_id < DAY (last_day(DATE_ADD( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ))) 
ORDER BYhelp_topic_id;

在这里插入图片描述

2、生成指定月份所有天数

select date_format(date_add('2022-02-01', interval(cast(help_topic_id as signed integer)+ 0) day),'%Y-%m-%d') 日期
from mysql.help_topic
where help_topic_id  < day(last_day('2022-02-01'))
order by help_topic_id;

3、生成当月第一天

select DATE_ADD(curdate(),interval -day(curdate())+1 day) as time

在这里插入图片描述

4、生成本周的日期

SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) as days ORDER BY days asc

在这里插入图片描述

生成本周日期带中文

SELECT CASE b.weekWHEN 1 THEN '星期一'WHEN 2 THEN '星期二'WHEN 3 THEN '星期三'WHEN 4 THEN '星期四'WHEN 5 THEN '星期五'WHEN 6 THEN '星期六'ELSE'星期日'
END as week,b.days FROM(SELECT (@i:=@i+1) week,a.* FROM (select @i:=0) as t,(SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) as days
UNION ALL SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) as days ORDER BY days asc ) as a)as b

在这里插入图片描述

5、生成本周第一天

SELECT  DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) as days

在这里插入图片描述

6、生成24小时

SELECTDATE_FORMAT( DATE_SUB( DATE_FORMAT( NOW(), '%Y-%m-%d' ), INTERVAL ( -( @i := @i + 1 ) ) HOUR ), '%Y-%m-%d %H' ) AS 'time' 
FROM(SELECTa FROM( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS aJOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1 ) AS b,(SELECT@i :=- 1 ) AS i