sql直接运行
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
生成本周日期带中文
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
5、生成本周第一天
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) as days
6、生成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