Mysql生成当月（指定月）所有天数 本周所有日期 当日24小时

Mysql生成当月（指定月）所有天数 本周所有日期 当日24小时

1. 生成当月所有天数

SELECT
	date_format(
		date_add( DATE_ADD( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 0 ) DAY ),
		'%Y-%m-%d' 
	) 日期 
FROM
	mysql.help_topic 
WHERE
	help_topic_id < DAY (
		last_day(
		DATE_ADD( curdate(), INTERVAL - DAY ( curdate())+ 1 DAY ))) 
ORDER BY
	help_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

5. 生成本周日期带中文

SELECT CASE b.week
	WHEN 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

6. 生成本周第一天

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

7. 生成24小时

SELECT
	DATE_FORMAT( DATE_SUB( DATE_FORMAT( NOW(), '%Y-%m-%d' ), INTERVAL ( -( @i := @i + 1 ) ) HOUR ), '%Y-%m-%d %H' ) AS 'time' 
FROM
	(
	SELECT
		a 
	FROM
		( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' ) AS a
		JOIN ( 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