MySQL 查询今天所有时间点、上周日期列表、本周日期列表、上月日期列表、本月日期列表、去年月份列表，本年月份列表、两个时间段之间的日期列表

最新推荐文章于 2022-09-24 19:53:44 发布

zhong_da

最新推荐文章于 2022-09-24 19:53:44 发布

阅读量475

点赞数 1

文章标签： mysql 数据库列表

本文链接：https://blog.csdn.net/zhong_da/article/details/116659436

版权

今天所有时间点

SELECT DATE_FORMAT( DATE_SUB( DATE_FORMAT( NOW(), '%Y-%m-%d' ), INTERVAL ( -( @i := @i + 1 ) ) HOUR ),  '%H' ) AS statTimeStr 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

上周日期列表

select a.date from (
			SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE-7, INTERVAL @a DAY)) AS `date`
			FROM mysql.help_topic,(SELECT @a := 0) temp
			WHERE @a  <![CDATA[<]]> 6 - WEEKDAY(CURRENT_DATE-7)
			UNION
			SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE -7, INTERVAL @s DAY)) AS `date`
			FROM mysql.help_topic,(SELECT @s := WEEKDAY(CURRENT_DATE) + 1) temp
			WHERE @s > 0
			ORDER BY `date`
			) as a

本周日期列表

select a.date from (
			SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
			FROM mysql.help_topic,(SELECT @a := 0) temp
			WHERE @a  <![CDATA[<]]> 6 - WEEKDAY(CURRENT_DATE)
			UNION
			SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
			FROM mysql.help_topic,(SELECT @s := WEEKDAY(CURRENT_DATE) + 1) temp
			WHERE @s > 0
			ORDER BY `date`
			) as a

上月日期列表

select a.date from (
			 SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(ADDDATE(CURRENT_DATE, INTERVAL -1 MONTH), INTERVAL @a day)) AS `date`
			 FROM mysql.help_topic,(SELECT @a := 0) temp
			 WHERE @a <![CDATA[<]]> DAYOFMONTH(last_day(ADDDATE(CURRENT_DATE, INTERVAL -1 MONTH))) - day(ADDDATE(CURRENT_DATE, INTERVAL -1 MONTH))
			 UNION
			 SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(ADDDATE(CURRENT_DATE, INTERVAL -1 MONTH), INTERVAL @s day)) AS `date`
			 FROM mysql.help_topic,(SELECT @s := day(ADDDATE(CURRENT_DATE, INTERVAL -1 MONTH)) ) temp
			 WHERE @s > 0
			 ORDER BY `date`
			 ) as a

本月日期列表

select a.date from (
			SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a day)) AS `date`
			FROM mysql.help_topic,(SELECT @a := 0) temp
			WHERE @a <![CDATA[<]]> DAYOFMONTH(last_day(curdate())) - day(CURRENT_DATE)
			UNION
			SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s day)) AS `date`
			FROM mysql.help_topic,(SELECT @s := day(CURRENT_DATE)) temp
			WHERE @s > 0
			ORDER BY `date`
			) as a

去年月份列表

select a.date from (
			 SELECT @a :=@a + 1 as `index`, date_format(ADDDATE(ADDDATE(CURRENT_DATE, INTERVAL -1 YEAR), INTERVAL @a MONTH),'%Y-%m') AS `date`
			 FROM mysql.help_topic,(SELECT @a := 0) temp
			 WHERE @a <![CDATA[<]]> 12 - MONTH(CURRENT_DATE)
			 UNION
			 SELECT @s :=@s - 1 as `index`, date_format(DATE_SUB(ADDDATE(CURRENT_DATE, INTERVAL -1 YEAR), INTERVAL @s MONTH),'%Y-%m') AS `date`
			 FROM mysql.help_topic,(SELECT @s := MONTH(CURRENT_DATE)) temp
			 WHERE @s > 0
			 ORDER BY `date`
			 ) as a

本年月份列表

select a.date from (
			SELECT @a :=@a + 1 as `index`, date_format(ADDDATE(CURRENT_DATE, INTERVAL @a MONTH),'%Y-%m') AS `date`
			FROM mysql.help_topic,(SELECT @a := 0) temp
			WHERE @a <![CDATA[<]]> 12 - MONTH(CURRENT_DATE)
			UNION
			SELECT @s :=@s - 1 as `index`, date_format(DATE_SUB(CURRENT_DATE, INTERVAL @s MONTH),'%Y-%m') AS `date`
			FROM mysql.help_topic,(SELECT @s := MONTH(CURRENT_DATE)) temp
			WHERE @s > 0
			ORDER BY `date`
			) as a

两个时间段之间的日期列表

select * from  (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) statTimeStr from
					(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
					(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
					(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
					(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
					(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
					where statTimeStr between 开始时间 and 结束时间