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

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

2022-10-21 16:00码奴生来只知道前进~ Mysql

最近常用到mysql的日期范围搜索,下面这篇文章主要给大家介绍了关于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 '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;

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

3、创建为视图之后. 可以通过视图查询时间区间列表日期

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

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

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查询时间区间日期列表实例代码

附：在对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!=’ ‘这个判断会报错,上面的是标准的时间查询,自己做的时候总是会加上!=’ ‘这个条件.所以总是报错,记录一下.