Mysql查詢時間區間日期列表範例程式碼

2022-04-19 19:03:24

1、查詢時間區間日期列表，不會由於資料表資料影響

select a.date 
from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.date between '2020-01-20' and '2021-12-24' ORDER BY a.date asc

tips：如果要查詢當前日期後面的資料 curdate()改為截止日期就好

2、建立檢視可以公共使用

CREATE VIEW v_digits AS
  SELECT 0 AS digit UNION ALL
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4 UNION ALL
  SELECT 5 UNION ALL
  SELECT 6 UNION ALL
  SELECT 7 UNION ALL
  SELECT 8 UNION ALL
  SELECT 9;
 
CREATE VIEW v_numbers AS
  SELECT
    ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
  FROM
    v_digits as ones,
    v_digits as tens,
    v_digits as hundreds,
    v_digits as thousands;
		
-- 生成的日期格式為  yyyy-MM-dd		
CREATE VIEW v_dates AS
  SELECT
    SUBDATE(CURRENT_DATE(), number) AS date
  FROM
    v_numbers
  UNION ALL
  SELECT
    ADDDATE(CURRENT_DATE(), number + 1) AS date
  FROM
    v_numbers;
 
-- 生成的日期格式為 yyyy-MM
CREATE VIEW v_months AS
  SELECT
    DATE_FORMAT(SUBDATE(CURRENT_DATE(), INTERVAL number MONTH),'%Y-%m')  AS date
  FROM
    v_numbers
  UNION ALL
  SELECT
    DATE_FORMAT(ADDDATE(CURRENT_DATE(), INTERVAL number+1 MONTH),"%Y-%m") AS date
  FROM
    v_numbers;

3、建立為檢視之後，可以通過檢視查詢時間區間列表日期

SELECT
  date
FROM
  v_dates
WHERE
  date BETWEEN '2020-01-20' AND '2021-01-24'
ORDER BY
  date asc

4、查詢時間區間按月

select DATE_FORMAT(str_to_date (a.Date,'%Y-%m-%d'),"%Y-%m") as Date 
from (
    select '2011-12-24' - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) MONTH as Date
    from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
    cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d
) a
where a.Date between '2010-01-20' and '2011-12-24' ORDER BY a.Date asc;

附：在對mysql的時間進行區間查詢的時候出現的問題

<if test="searchcondition.starttime!=null">
    <![CDATA[ and select_data.data_time  >= #{searchcondition.starttime,jdbcType=TIMESTAMP} ]]>
</if>

<if test="searchcondition.stoptime!=null">
    <![CDATA[ and select_data.data_time <= #{searchcondition.stoptime,jdbcType=TIMESTAMP} ]]>
</if>

在test中不能使用searchcondition.stoptime!=’ ‘這個判斷會報錯,上面的是標準的時間查詢,自己做的時候總是會加上!=’ ‘這個條件.所以總是報錯,記錄一下.

總結

到此這篇關於Mysql查詢時間區間日期列表的文章就介紹到這了,更多相關Mysql查詢時間區間日期列表內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！