Mybatis查询年、季度、月、日、最近七天、今天、昨天、7天内、30天的数据
·
〇、查询今天、昨天、7天内、30天的数据（存储使用较多）
今天的所有数据：
select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=0
昨天的所有数据：
select * from 表名 where DateDiff(dd,datetime类型字段,getdate())=1
7天内的所有数据：
select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=7
30天内的所有数据：
select * from 表名 where DateDiff(dd,datetime类型字段,getdate())<=30
本月的所有数据：
select * from 表名 where DateDiff(mm,datetime类型字段,getdate())=0
本年的所有数据：
select * from 表名 where DateDiff(yy,datetime类型字段,getdate())=0
查询今天是今年的第几天：
select datepart(dayofyear, getDate())
查询今天是本月的第几天：
1. select datepart(dd, getDate())
2.select day(getDate())
查询本周的星期一日期是多少 （注意：指定日期不能是周日，如果是周日会计算到下周一去。所以如果是周日要减一天）
SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
查询昨天日期：
select convert(char,dateadd(DD,-1,getdate()),111) //111是样式号，（100-114）
查询本月第一天日期：
Select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as firstday
查询本月最后一天日期：
Select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) as lastday //修改-3的值会有相应的变化
本月有多少天：
select datepart(dd, dateadd(dd,-1,dateadd(mm,1,cast((cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' ) as datetime ))))
求两个时间段相差几天：
select datediff(day, '2016/5/17', '2016/8/20') as daysum
在指定的日期上±N天：
select convert(char, dateadd(dd, 1, '2016/8/20'), 111) as kssj //输出2016/8/21
在指定的日期上±N分钟：
select dateadd(mi, -15, getdate()) //查询当前时间15分钟之前的日期
一、时间范围: 1 ==> 年; 2 ==> 月; 3 ==> 周
<select id="getBroadcastStatistics" resultType="com.rd.pojo.vo.BraodcastStatsVo">
SELECT
sum(bd.cog_id = '4') AS total,
sum(bd.proc_state = '1') AS ban,
sum(bd.proc_state = '2') AS disapper,
sum(bd.label_state = '0') AS new
FROM broadcast bd
<where>
<if test="timeType == 1">
DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= DATE(last_appeartime);
</if>
<if test="timeType == 2">
DATE_SUB(CURDATE(), INTERVAL 1 MONTH ) <= DATE(last_appeartime);
</if>
<if test="timeType == 3">
DATE_SUB(CURDATE(), INTERVAL 1 WEEK ) <= DATE(last_appeartime);
</if>
</where>
</select>
二、时间范围: ： 0：今日，1：周，2：月，3：自定义，4：最近七天
声明：classType------0：今日，1：周，2：月，3：自定义，4：最近七天
<choose>
<when test="classType == 1">
AND DATE_FORMAT(s.create_time,'%Y%u') = DATE_FORMAT(CURDATE( ),'%Y%u')
</when>
<when test="classType == 2">
AND DATE_FORMAT(s.create_time,'%Y%m') = DATE_FORMAT(CURDATE( ),'%Y%m')
</when>
<when test="classType == 3">
<choose>
<when test="beginTime!=null and beginTime!='' and endTime == '' ">
AND Date(s.create_time) between #{beginTime,jdbcType=VARCHAR} and CURDATE()
</when>
<when test="endTime!=null and endTime!='' and beginTime == '' ">
AND Date(s.create_time) <= #{endTime,jdbcType=VARCHAR}
</when>
<when test="beginTime!=null and beginTime!='' and endTime!=null and endTime!= '' ">
AND Date(s.create_time) between #{beginTime,jdbcType=VARCHAR} and #{endTime,jdbcType=VARCHAR}
</when>
<otherwise>
AND Date(s.create_time) = CURDATE()
</otherwise>
</choose>
</when>
<when test="classType == 4">
AND date(s.create_time) between date_sub(curdate(), INTERVAL 6 DAY) and curdate()
</when>
<otherwise>
AND Date(s.create_time) = CURDATE()
</otherwise>
</choose>
三、时间范围: y年，s季，m月 ，天
/*按照年度查询*/
<if test="params.selected == 'y'.toString() and params.beginTime != null and params.beginTime != '' ">
AND Year(create_time) >=#{params.beginTime}
</if>
/**结束年度查询*/
<if test="params.selected == 'y'.toString() and params.endTime != null and params.endTime != '' ">
AND Year(create_time) <= #{params.endTime}
</if>
/**按照季度查询*/
<if test="params.selected == 's'.toString() and params.beginTime != null and params.beginTime != '' ">
AND concat(Year(create_time),quarter(create_time)) >= concat(Year(#{params.beginTime}),day(#{params.beginTime}))
</if>
/**结束季度查询*/
<if test="params.selected == 's'.toString() and params.endTime != null and params.endTime != '' ">
AND concat(Year(create_time),quarter(create_time)) <= concat(Year(#{params.endTime}),day(#{params.endTime}))
</if>
/**按照月份查询*/
<if test="params.selected == 'm'.toString() and params.beginTime != null and params.beginTime != '' ">
AND date_format(create_time,'%y%m') >=date_format(#{params.beginTime},'%y%m')
</if>
/**结束月份查询*/
<if test="params.selected == 'm'.toString() and params.endTime != null and params.endTime != '' ">
AND date_format(create_time,'%y%m') <= date_format(#{params.endTime},'%y%m')
</if>
<!-- 按照天查询-->
<if test="params.selected == 'd'.toString() and params.beginTime != null and params.beginTime != '' ">
AND date_format(create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
</if>
<!-- 结束天查询-->
<if test="params.selected == 'd'.toString() and params.endTime != null and params.endTime != '' ">
AND date_format(create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
</if>
四、时间范围: y年，s季，m月 ，其他
<choose>
<when test="params.selected ='y'">
<if test="params.beginTime != null and params.beginTime != '' ">
AND Year(create_time) >=#{params.beginTime}
</if>
<if test="params.endTime != null and params.endTime != '' ">
AND Year(create_time) <= #{params.endTime}
</if>
</when>
<when test="params.selected ='s'">
<if test="params.beginTime != null and params.beginTime != '' ">
AND concat(Year(create_time),quarter(create_time)) >= concat(Year(#{params.beginTime}),day(#{params.beginTime}))
</if>
<if test="params.endTime != null and params.endTime != '' ">
AND concat(Year(create_time),quarter(create_time)) <= concat(Year(#{params.endTime}),day(#{params.endTime}))
</if>
</when>
<when test="params.selected ='m'">
<if test="params.beginTime != null and params.beginTime != '' ">
AND date_format(create_time,'%y%m') >=date_format(#{params.beginTime},'%y%m')
</if>
<if test="params.endTime != null and params.endTime != '' ">
AND date_format(create_time,'%y%m') <= date_format(#{params.endTime},'%y%m')
</if>
</when>
<otherwise>
<where>
<if test="params.beginTime != null and params.beginTime != '' ">
AND date_format(create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
</if>
<if test="params.endTime != null and params.endTime != '' ">
AND date_format(create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
</if>
</where>
</otherwise>
</choose>
五、传入时间范围参数类型是字符串
<if test="startTime!=null and startTime.trim() neq ''">
and date_format(create_time,'%Y-%m-%d %H:%i:%s') >= str_to_date(#{startTime},'%Y-%m-%d %H:%i:%s')
</if>
<if test="endTime!=null and endTime.trim() neq ''">
and date_format(create_time,'%Y-%m-%d %H:%i:%s') <= str_to_date(#{endTime},'%Y-%m-%d %H:%i:%s')
</if>
或者
<if test="beginTime != null and beginTime !='' ">
and <![CDATA[p.create_time >=DATE_ADD(#{beginTime },interval 1 day)]]>
</if>
<if test="endTime != null and endTime !=''">
and <![CDATA[p.create_time <=DATE_ADD(#{endTime},interval 1 day)]]>
</if>
六、传入时间范围参数类型是Date
<if test="startTime!=null and startTime.trim() neq ''">
and date_format(create_time,'%Y-%m-%d %H:%i:%s') >= date_format(#{startTime},'%Y-%m-%d %H:%i:%s')
</if>
<if test="endTime!=null and endTime.trim() neq ''">
and date_format(create_time,'%Y-%m-%d %H:%i:%s') <= date_format(#{endTime},'%Y-%m-%d %H:%i:%s')
</if>
七、Mybatis-Plus时间范围查询
Page<Record> page = new Page<>(page, limit);
IPage<Record> result = iRecordService.page(page,
new LambdaQueryWrapper<Record>()
.apply(StrUtil.isNotBlank(start_date),
"date_format (create_time,'%Y-%m-%d %H:%i:%s') >= date_format('" + start_date + "','%Y-%m-%d %H:%i:%s')")
.apply(StrUtil.isNotBlank(end_date),
"date_format (create_time,'%Y-%m-%d %H:%i:%s') <= date_format('" + end_date + "','%Y-%m-%d %H:%i:%s')")
.orderByDesc(HmsFaceDetectLog::getOptime));
八、字符串转换成日期 、日期转换成字符串
//字符串转换成日期
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = null;
try {
date = format.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
//日期转换成字符串
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String str = format.format(date);
旨在为数千万中国开发者提供一个无缝且高效的云端环境，以支持学习、使用和贡献开源项目。
更多推荐
6
0- 0
已为社区贡献5条内容
所有评论(0)