sql查询时间范围数据

article/2025/9/21 5:17:29

mapper.xml ==>查询一定时间范围
条件:年月日时分秒

        <if test="creatAt != null and creatAt !='' "><![CDATA[and  DATE_FORMAT(ride_order.created_at, '%Y-%m-%d %h-%m-%s')>= DATE_FORMAT(#{creatAt}, '%Y-%m-%d %h-%m-%s')
]]></if><if test="endAt != null and endAt !='' "><![CDATA[and  DATE_FORMAT(ride_order.updated_at, '%Y-%m-%d %h-%m-%s')<= DATE_FORMAT(#{endAt}, '%Y-%m-%d %h-%m-%s')
]]></if>

查询一定时间范围

SELECT users.* 
FROM   users
WHERE  create_time >= '2021-12-01 00:00:00' 
AND  create_time <= '2021-12-06 00:00:00'
或者
SELECT users.* 
FROM   users
WHERE  create_time 
BETWEEN '2021-12-01' AND '2021-12-07';

查询当月时间

SELECT
* 
FROM表
WHEREDATE_FORMAT( create_time(时间), '%Y%m' ) = DATE_FORMAT( CURDATE(), '%Y%m' )

查询最近7天的

SELECT* 
FROMt_garden_ranking_enery t 
WHEREDATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( create_time );

查询今天的

SELECT* 
FROMt_garden_ranking_enery t 
WHEREto_days(create_time) = to_days(now());// 或者date(create_time) = curdate();

查询本周

SELECT* 
FROMt_garden_ranking_enery t 
WHEREYEARWEEK(date_format( t.create_time, '%Y-%m-%d' )) = YEARWEEK(now())

查询上周

SELECT* 
FROMt_garden_ranking_enery t 
WHEREYEARWEEK(date_format( t.create_time, '%Y-%m-%d' )) = YEARWEEK(now())-1

STR_TO_DATE(#{endTime}, '%Y-%m-%d'DATE_FORMAT(NOW(),'%Y-%m-%d')

去除后面的时分秒
在这里插入图片描述

select date_format(时间的字段名,'%Y-%m-%d') 
from 表名;
SELECTdate_format( F_DT_DATA_CREATE_TIME, '%Y-%m-%d' ) 
FROMt_eim_qianbao;

  SELECT a.F_VC_PROJECT_ID,a.F_VC_QIANBAO_ID,b.F_VC_NAME,b.F_INT_TYPE,b.F_VC_URL,date_format(b.F_DT_DATA_CREATE_TIME, '%Y-%m-%d' ) ,date_format(b.F_DT_DATA_UPDATE_TIME, '%Y-%m-%d' ) FROM t_eim_qianbao_relation AS a,t_eim_qianbao AS bWHERE a.F_VC_QIANBAO_ID = b.F_VC_IDAND a.F_VC_PROJECT_ID = "069e97da1cb729fb52030442d735f458"AND b.F_INT_DATA_DEL_FLAG=0