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:统计每小时创建的个数

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:比较时间

    <select id="queryByStatusAndTime" parameterType="map" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from pdca
        <where>

            begin_time is not null and end_time is not null

            <if test="userId != null and userId != '' ">
                and user_id=#{userId}
            </if>

            <if test="status != null">
                and status in
                <foreach collection="status" item="s" open="(" separator="," close=")">
                    #{s}
                </foreach>
            </if>

            <if test="finishedTime != null and finishedTime != ''">
                and date_format(finished_time, '%Y-%m')=date_format(#{finishedTime}, '%Y-%m')
            </if>

            <if test="endTime != null and endtime != ''">
                and date_format(end_time, '%Y-%m-%d')=date_format(#{endTime}, '%Y-%m-%d')
            </if>

            <if test="endTimeLt != null and endtimeLt != ''">
                and end_time &lt; #{endTimeLt}
            </if>

            <if test="endTimeGt != null and endtimeGt != ''">
                and end_time &gt; #{endTimeGt}
            </if>

            <if test="beginTime != null and beginTime != ''">
                and date_format(begin_time, '%Y-%m-%d')=date_format(#{beginTime}, '%Y-%m-%d')
            </if>

            <if test="completion != null">
                and completion=#{completion}
            </if>
        </where>
    </select>