Mysql查询时间区间日期列表实例代码

目录

1、查询时间区间日期列表，不会由于数据表数据影响2、创建视图可以公共使用3、创建为视图之后，可以通过视图查询时间区间列表日期4、查询时间区间按月附：在对mysql的时间进行区间查询的时候出现的问题总结

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 ‘2022-01-20’ and ‘2022-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 BEТWEEN ‘2022-01-20’ AND ‘2022-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 ‘2022-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=TIMESТAMP} ]]>

</if>

<if test=”searchcondition.stoptime!=null”>

    <![CDATA[ and select_data.data_time <= #{searchcondition.stoptime,jdbcType=TIMESТAMP} ]]>

</if>在test中不能使用searchcondition.stoptime!=’ ‘这个判断会报错,上面的是标准的时间查询,自己做的时候总是会加上!=’ ‘这个条件.所以总是报错,记录一下.

总结

到此这篇关于Mysql查询时间区间日期列表的文章就介绍到这了,更多相关Mysql查询时间区间日期列表内容请搜索共生网络以前的文章或继续浏览下面的相关文章希望大家以后多多支持共生网络！