sql查询时间范围数据

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
	表
WHERE
	DATE_FORMAT( create_time(时间), '%Y%m' ) 
 = DATE_FORMAT( CURDATE(), '%Y%m' ) 

查询最近7天的

SELECT
	* 
FROM
	t_garden_ranking_enery t 
WHERE
	DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( create_time );

查询今天的

SELECT
	* 
FROM
	t_garden_ranking_enery t 
WHERE
	to_days(create_time) = to_days(now());
 // 或者date(create_time) = curdate();

查询本周

SELECT
	* 
FROM
	t_garden_ranking_enery t 
WHERE
	YEARWEEK(
	date_format( t.create_time, '%Y-%m-%d' )) = YEARWEEK(
	now())

查询上周

SELECT
	* 
FROM
	t_garden_ranking_enery t 
WHERE
	YEARWEEK(
	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 表名;
SELECT
	date_format( F_DT_DATA_CREATE_TIME, '%Y-%m-%d' ) 
FROM
	t_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 b
        WHERE a.F_VC_QIANBAO_ID = b.F_VC_ID
          AND a.F_VC_PROJECT_ID = "069e97da1cb729fb52030442d735f458"
          AND b.F_INT_DATA_DEL_FLAG=0

转换格式,需要由data 转换成string