如果你要严格要求是某一年的,那可以这样
查询一天:
select * from table where to_days(column_time) = to_days(now());
select * from table where date(column_time) = curdate();
查询一周:
select * from table where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column_time);
查询一个月:
select * from table where DATE_SUB(CURDATE(), INTERVAL 1 MONTH) <= date(column_time);
查询某一个月
select * from exam where date_format(starttime,'%Y-%m')='2017-10'
select * from exam where date_format(starttime,'%Y-%m')=date_format('2017-10-05','%Y-%m')
查询某一天
select * from exam where date_format(starttime,'%Y-%m-%d')=date_format('2017-10-05','%Y-%m-%d')
查询一年:
select * from table where DATE_SUB(CURDATE(), INTERVAL 1 YEAR) <= date(column_time);
两个字段
方法一
SELECT
topic_id as topicId,id,
count( topic_id ) AS topicSum
FROM
topic_memo
WHERE
STATUS = 1
AND memo_type = 1
AND actual_time IS NULL
AND user_id = 4
AND '2022-11-19' BETWEEN begin_time and end_time
GROUP BY
topic_id
方法二
SELECT
topic_id as topicId,id,
count( topic_id ) AS topicSum
FROM
topic_memo
WHERE
STATUS = 1
AND memo_type = 1
AND actual_time IS NULL
AND user_id = 4
AND begin_time <='2022-11-11' and '2022-11-11' <=end_time
GROUP BY
topic_id
判断是否满足某个条件
<select id="selectByTeamId" resultType="java.lang.Integer">
select count(*) from jf_consultation_record as a where
doctorId=#{id} and cstate=0
<if test="typeId!=null and typeId=='1'.toString()">
and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <![CDATA[ <= ]]> date(ctime)
</if>
<if test="typeId!=null and typeId=='2'.toString()">
and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <![CDATA[ <= ]]> date(ctime)
</if>
<if test="typeId!=null and typeId=='3'.toString()">
and DATE_SUB(CURDATE(), INTERVAL INTERVAL 3 MONTH) <![CDATA[ <= ]]> date(ctime)
</if>
</select>
-- 时间段查询
<!-- <if test="taskTime!=null">-->
<!-- AND date_format(a.task_start_time,'%Y-%m-%d')= date_format(#{taskTime},'%Y-%m-%d')-->
<!-- </if>-->
<if test="taskTime != null">
and date_format(a.task_start_time, '%Y-%m-%d') >= date_format(#{taskTime},'%Y-%m-%d')
</if>
<if test="taskEndTime != null">
and date_format(a.task_start_time, '%Y-%m-%d') <= date_format(#{taskEndTime},'%Y-%m-%d')
</if>