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查询时间区间日期列表内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!
声明:如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。