Mysql查询时间区间日期列表实例代码
作者:码奴生来只知道前进~ 时间:2024-01-17 16:17:32
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!=’ ‘这个判断会报错,上面的是标准的时间查询,自己做的时候总是会加上!=’ ‘这个条件.所以总是报错,记录一下.
来源:https://blog.csdn.net/tanqingfu1/article/details/121656404
标签:mysql,时间,区间
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
GPU状态监测 nvidia-smi 命令的用法详解
2022-08-28 20:57:37
![](https://img.aspxhome.com/file/2023/7/135417_0s.png)
Python如何实现SSH远程连接与文件传输
2023-06-12 12:34:02
vue实现导航栏下拉菜单
2024-05-09 15:17:56
![](https://img.aspxhome.com/file/2023/2/126482_0s.gif)
python导入csv文件出现SyntaxError问题分析
2023-12-12 04:29:57
Oracle中执行动态SQL
2024-01-19 20:55:37
![](https://img.aspxhome.com/file/2023/7/66547_0s.jpg)
Python实现对桌面进行实时捕捉画面的方法详解
2022-09-06 19:12:59
Python rstrip()方法实例详解
2021-05-16 10:05:40
javascript自定义加载loading效果
2024-04-27 15:23:14
![](https://img.aspxhome.com/file/2023/0/135500_0s.png)
Python科学计算包numpy用法实例详解
2021-11-12 15:07:20
js判断传入时间和当前时间大小实例(超简单)
2024-05-02 17:26:40
pytorch自定义不可导激活函数的操作
2022-07-05 10:09:13
![](https://img.aspxhome.com/file/2023/5/77015_0s.png)
JavaScript html5 canvas实现图片上画超链接
2024-04-28 10:19:19
PHP For循环字母A-Z当超过26个字母时输出AA,AB,AC
2023-10-07 08:09:10
python向企业微信发送文字和图片消息的示例
2021-09-18 15:42:08
![](https://img.aspxhome.com/file/2023/8/131078_0s.jpg)
JS中数组重排序方法
2024-04-22 22:34:27
python timestamp和datetime之间转换详解
2021-02-07 11:17:51
Oracle密码文件的使用和维护第1/3页
2010-07-30 12:43:00
浮动元素对浏览器的支持
2008-02-02 10:05:00
golang将切片或数组根据某个字段进行分组操作
2024-05-02 16:24:42
详解MySQL分组链接的使用技巧
2024-01-14 03:40:37
![](https://img.aspxhome.com/file/2023/2/107412_0s.png)