MySQL时间查询讲解+实战教学（查询本月、上个月、下个月等等的数据）

MySql时间查询

MySql查询当前时间

• 查询 年-月-日 时:分:秒
  select now()

• 查询 年-月-日
  select DATE(CURDATE())

• 查询 年-月
  select date_format(NOW(),'%Y-%m')

• 查询当前年
  select YEAR(CURDATE())

• 查询当前月
  select MONTH(CURDATE())

• 查询当前日
  select DAYOFMONTH(NOW())

• 查询当前星期几
  select dayofweek(NOW()) -1

• 查询当前季度
  select QUARTER(now())

• 查询 时:分:秒
  select current_time()

• 万能公式：DATE_FORMAT
  select DATE_FORMAT(now(),'%Y-%m-%d %h:%i:%s')

MySql查询上一年、上一个月、上一个星期…

• 查询昨天的数据
  select * from test as 'time' where time.update_time <DATE_SUB(CURDATE(), INTERVAL 1 DAY)

• 查询当前这周的数据

  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 0 WEEK)

  select * FROM test as 'time' where YEARWEEK(date_format(time.update_time,’%Y-%m-%d’)) = YEARWEEK(now())

• 查询上一周的数据
  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 WEEK)

  select * FROM test as 'time' WHERE YEARWEEK(date_format(time.update_time,’%Y-%m-%d’)) = YEARWEEK(now())-1

• 查询上一个月的数据
  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 MONTH)

• 查询近两个月的数据（INTERVAL后面的数字改一下就可以了）
  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 2 MONTH)

• 查询上一年的数据
  select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 YEAR)

• 查询距离当前现在6个月的数据

  select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

MySql查询本年、本月、本周…

• 查询今天的数据
  select * from test as time where to_days(time.update_time ) =to_days(now());

• 查询本周的数据
  select * FROM test as time WHERE YEARWEEK(date_format(time.update_time ,'%Y-%m-%d')) = YEARWEEK(now());

• 查询本月的数据
  select * FROM test as time WHERE DATE_FORMAT(time.update_time , '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

• 查询本季度的数据
  select * from test as time where QUARTER(time.update_time)=QUARTER(now());

• 查询本年的数据
  select * from test as time where YEAR(time.update_time)=YEAR(NOW());

实战：生成这样的一个数据表，根据月份来输出值

有两个关键的数据，一个是月份，一个是数据，我们要的效果，每个月累加（比如6月份有一个人，但7月份没有，但是是累加的，所以7月份有一个，12月份新增三个，所以总共是4）

思考：

第一点：月份：要通过本月来向前面递减，重点用UNION连接函数

-- 近1个月SELECT DATE_FORMAT( CURDATE( ), '%Y-%m' ) AS `month` UNION-- 近2个月SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `month` UNION-- 近3个月SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `month` UNIONSELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `month` UNIONSELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `month` UNION-- 近N个月,N等于"INTERVAL 5 MONTH"中间的数值`5`加`1`，此处N=6SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `month`

整理一下，因为后续我们还连接数据，所以要用v来指代这个数据

SELECTCONCAT(RIGHT(v.`month`,2),'月') AS `time`FROM(-- 近1个月SELECT DATE_FORMAT( CURDATE( ), '%Y-%m' ) AS `month` UNION-- 近2个月SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `month` UNION-- 近3个月SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `month` UNIONSELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `month` UNIONSELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `month` UNION-- 近N个月,N等于"INTERVAL 5 MONTH"中间的数值`5`加`1`，此处N=6SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `month`) AS v

第二点：数据：计算近六个月的数据（如下）

 SELECT DATE_FORMAT(a.create_time, '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS a-- 计算近六个月的时间 WHERE DATE_FORMAT(a.create_time, '%Y-%m') > DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH ), '%Y-%m')-- 补充查询匹配条件AND a.is_deleted = 1GROUP BY `month`

但是这样是不对的，这个是分别查询六个月的数据，不是累加的（需要这种的朋友上面代码自取），所以我们要改一下思路

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS a-- 计算12月及其之前的时间 （现在2022年12月份 DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') = 2022-11）-- 这是是需要计算 一月份之前，要包含12月份WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL -1 MONTH ), '%Y-%m')-- 补充查询匹配条件AND a.is_deleted = 1

好了，但这只有一条数据，我们需要再累加一下

SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS a-- 计算12月及其之前的时间 （现在2022年12月份 DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') = 2022-11）-- 这是是需要计算 一月份之前，要包含12月份WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL -1 MONTH ), '%Y-%m')-- 补充查询匹配条件AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION-- 下面都是cv了，sql没想到什么好的办法，但在mybatis中可以优化，之后我会讲到SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2 UNIONSELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2 UNIONSELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2 UNIONSELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2 UNIONSELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2

把之前的结合一下

SELECTIFNULL( b.count, 0 ) AS `count`,CONCAT(RIGHT(v.`month`,2),'月') AS `time`FROM(-- 近1个月SELECT DATE_FORMAT( CURDATE( ), '%Y-%m' ) AS `month` UNION-- 近2个月SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `month` UNION-- 近3个月SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `month` UNIONSELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `month` UNIONSELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `month` UNION-- 近N个月,N等于"INTERVAL 5 MONTH"中间的数值`5`加`1`，此处N=6SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `month`) AS v LEFT JOIN(SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS a-- 计算12月及其之前的时间 （现在2022年12月份 DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') = 2022-11）-- 这是是需要计算 一月份之前，要包含12月份WHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL -1 MONTH ), '%Y-%m')-- 补充查询匹配条件AND a.is_deleted = 1 AND a.jurisdiction = 2 UNION-- 下面都是cv了，sql没想到什么好的办法，但在mybatis中可以优化，之后我会讲到SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 0 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2 UNIONSELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 1 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2 UNIONSELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 2 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2 UNIONSELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 3 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2 UNIONSELECT DATE_FORMAT(( CURDATE( ) - INTERVAL 5 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS aWHERE DATE_FORMAT(a.create_time, '%Y-%m') < DATE_FORMAT(( CURDATE( ) - INTERVAL 4 MONTH ), '%Y-%m')AND a.is_deleted = 1AND a.jurisdiction = 2AND a.is_deleted = 1AND a.jurisdiction = 2) AS b ON v.`month` = b.`month`GROUP BY `time` ORDER BY `time` ASC

放入mybatis中优化一下

实体类:

@Data@Accessors(chain = true)@AllArgsConstructor@NoArgsConstructorpublic class PeopleCountVO {private Integer count;private String time;private Integer jurisdiction;private List<Integer> math;}

xml:根据math的数据动态的修改，比如math=[1,2,3,4,5,6,7,8,9,10,11,12]，就是查询12个月的数据

<select id="peopleCount" parameterType="com.zhao.pojo.vo.PeopleCountVO" resultType="com.zhao.pojo.vo.PeopleCountVO">SELECTIFNULL( b.count, 0 ) AS `count`,v.`month` AS `time`FROM(-- 查询 近六个月<foreachcollection="people.math" separator=" UNION " item="item">SELECT DATE_FORMAT( ( CURDATE( ) - INTERVAL #{item} - 1 MONTH ), '%Y-%m' ) AS `month`</foreach>)AS v LEFT JOIN(<foreach collection="people.math" separator=" UNION " item="item">SELECT DATE_FORMAT(( CURDATE( ) - INTERVAL #{item}-1 MONTH ), '%Y-%m') AS `month`,COUNT( * ) AS `count`FROM `user` AS a-- 小于符号 在xml中会报错 使用 <span class="token entity named-entity" title="< 转义一下WHERE DATE_FORMAT(a.create_time, '%Y-%m') <span class="token entity named-entity" title="< DATE_FORMAT(( CURDATE( ) - INTERVAL #{item}-2 MONTH ), '%Y-%m')-- 补充查询匹配条件AND a.is_deleted = 1 AND a.jurisdiction = #{people.jurisdiction}</foreach>) AS bON v.`month` = b.`month`GROUP BY `time` ORDER BY `time` ASC</select>

参考文章：数据库查询当前时间大全（MySQL、Oracle、Sqlserver）_小豆的编程世界…的博客-CSDN博客_数据库时间