mysql时间字符串按年/月/天/时分组查询 -- date_format

SELECT DATE_FORMAT( deteline, "%Y-%m-%d %H" ) , COUNT( * ) 

FROM test

GROUP BY DATE_FORMAT( deteline, "%Y-%m-%d %H" ) 

查询某天：

deteline, "%Y-%m-%d"

某时：

deteline, "%Y-%m-%d %H"

依次类推。

其实就是对dateline进行处理，然后再对处理后的数据分组

 

ex:统计每小时创建的个数

1 SELECT DATE_FORMAT( create_time, "%Y-%m-%d %H" ) as time , COUNT( * ) as count FROM pdca GROUP BY DATE_FORMAT( create_time, "%Y-%m-%d %H" )

 Ex2:比较时间

 1     <select id="queryByStatusAndTime" parameterType="map" resultMap="BaseResultMap">
 2         select
 3         <include refid="Base_Column_List"/>
 4         from pdca
 5         <where>
 6 
 7             begin_time is not null and end_time is not null
 8 
 9             <if test="userId != null and userId != '' ">
10                 and user_id=#{userId}
11             </if>
12 
13             <if test="status != null">
14                 and status in
15                 <foreach collection="status" item="s" open="(" separator="," close=")">
16                     #{s}
17                 </foreach>
18             </if>
19 
20             <if test="finishedTime != null and finishedTime != ''">
21                 and date_format(finished_time, '%Y-%m')=date_format(#{finishedTime}, '%Y-%m')
22             </if>
23 
24             <if test="endTime != null and endtime != ''">
25                 and date_format(end_time, '%Y-%m-%d')=date_format(#{endTime}, '%Y-%m-%d')
26             </if>
27 
28             <if test="endTimeLt != null and endtimeLt != ''">
29                 and end_time &lt; #{endTimeLt}
30             </if>
31 
32             <if test="endTimeGt != null and endtimeGt != ''">
33                 and end_time &gt; #{endTimeGt}
34             </if>
35 
36             <if test="beginTime != null and beginTime != ''">
37                 and date_format(begin_time, '%Y-%m-%d')=date_format(#{beginTime}, '%Y-%m-%d')
38             </if>
39 
40             <if test="completion != null">
41                 and completion=#{completion}
42             </if>
43         </where>
44     </select>

 

转载于:https://www.cnblogs.com/wihainan/p/6187653.html