mysql 日期相关查询

查询指定时间段内所有日期

select fd_time from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) AS fd_time 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 fd_time between date_sub( SYSDATE(), INTERVAL 11 DAY ) and date_sub( SYSDATE(), INTERVAL 1 DAY ) 
ORDER BY fd_time

查询指定时间段内，所有月份

select date_format(`date`, '%Y-%m') fd_time
from (select adddate('1970-01-01', interval
            t2.i * 100 + t1.i * 10 + t0.i month) `date`
      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) a
where `date` between date(DATE_SUB(CURDATE(),INTERVAL 10 MONTH)) and CURDATE()

查询当前年，所有月份

SELECT months
FROM
	( SELECT CASE WHEN length( mon )= 1 
	THEN concat( CONCAT( YEAR ( CURDATE()), '-0' ), mon ) 
	ELSE concat( CONCAT( YEAR ( CURDATE()), '-' ), mon )  END months 
FROM
	( SELECT @m := @m + 1 mon FROM sys_user,( SELECT @m := 0 ) a ) aa 
	LIMIT 12 
	) a

查询当前财年所有月份，当前财年月份4月-明年3月，判断当前处于哪个财年时间段，注意，sys_user替换成一个数据超过12条的表

SELECT
CASE
		
	WHEN MONTH
		(
		date_sub( CURDATE(), INTERVAL 1 DAY )) BETWEEN 4 
		AND 12 THEN
		IF
			(
				SUBSTR( a.months FROM 6 FOR 7 ) + 0 BETWEEN 1 
				AND 3,
				CONCAT(
					SUBSTR( a.months FROM 1 FOR 4 ) + 1,
				SUBSTR( a.months FROM 5 )),
				a.months 
			) 
			WHEN MONTH (
			date_sub( CURDATE(), INTERVAL 1 DAY )) BETWEEN 1 
			AND 3 THEN
			IF
				(
					SUBSTR( a.months FROM 6 FOR 7 ) + 0 BETWEEN 4 
					AND 12,
					CONCAT(
						SUBSTR( a.months FROM 1 FOR 4 ) + 1,
					SUBSTR( a.months FROM 5 )),
					a.months 
				) ELSE a.months 
			END months 
FROM
	(
	SELECT
	CASE
			
		WHEN
			length( mon )= 1 THEN
				concat( CONCAT( YEAR ( date_sub( CURDATE(), INTERVAL 1 DAY )), '-0' ), mon ) ELSE concat( CONCAT( YEAR ( date_sub( CURDATE(), INTERVAL 1 DAY )), '-' ), mon ) 
			END months 
FROM
	( SELECT @m := @m + 1 mon FROM sys_user,( SELECT @m := 0 ) a ) aa 
	LIMIT 12 
	) a

SELECT CASE WHEN MONTH ( SYSDATE()) BETWEEN 4  AND 12 THEN IF ( SUBSTR( a.months FROM 6 FOR 7 ) + 0 BETWEEN 1  AND 3,
				CONCAT( SUBSTR( a.months FROM 1 FOR 4 ) + 1, SUBSTR( a.months FROM 5 )), a.months  ) 
 WHEN MONTH ( SYSDATE()) BETWEEN 1 AND 3 THEN IF ( SUBSTR( a.months FROM 6 FOR 7 ) + 0 BETWEEN 4  AND 12,
 CONCAT( SUBSTR( a.months FROM 1 FOR 4 ) + 1, SUBSTR( a.months FROM 5 )), a.months  ) ELSE a.months  END months 
FROM ( SELECT CASE WHEN length( mon )= 1 THEN concat( CONCAT( YEAR ( SYSDATE()), '-0' ), mon ) ELSE concat( CONCAT( YEAR ( SYSDATE()), '-' ), mon )  END months 
FROM ( SELECT @m := @m + 1 mon FROM sys_user,( SELECT @m := 0 ) a ) aa  LIMIT 12  ) a

查询指定日期前一周最后一天和前一月月最后一天

昨天日期，上周最后一天
SELECT DATE( subdate(date_sub( SYSDATE(), INTERVAL 1 DAY ),date_format(date_sub( SYSDATE(), INTERVAL 1 DAY ),'%w')) );
昨天日期，上月最后一天
SELECT DATE(last_day(DATE_SUB(date_sub( SYSDATE(), INTERVAL 1 DAY ),INTERVAL 1 MONTH)));

查询指定日期所在月份全部日期

SELECT
		@a := @a + 1 AS `index`,
		DATE(
		ADDDATE( date_sub( CURRENT_DATE, INTERVAL 1 DAY ), INTERVAL @a DAY )) AS `fd_time` 
	FROM
		mysql.help_topic,(
		SELECT
			@a := 0 
		) temp 
	WHERE
		  DAY (
			LAST_DAY(
			date_sub( CURRENT_DATE, INTERVAL 1 DAY ))) - DAY (
		date_sub( CURRENT_DATE, INTERVAL 1 DAY )) > @a UNION
	SELECT
		@s := @s - 1 AS `index`,
		DATE(
		DATE_SUB( date_sub( CURRENT_DATE, INTERVAL 1 DAY ), INTERVAL @s DAY )) AS `fd_time` 
	FROM
		mysql.help_topic,(
		SELECT
			@s := DAY (
			date_sub( CURRENT_DATE, INTERVAL 1 DAY ))) temp 
	WHERE
		@s > 0 
	ORDER BY
		`fd_time`