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进行处理，然后再对处理后的数据分组

 

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

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" )

　

from sqlalchemy import func

with DBContext('w') as session:
    start_date = Common.get_month_and_day(day, '%Y-%m-%d 00:00:00')
    items = session.query(func.date_format(GitOperateLog.gol_datetime, '%Y-%m-%d'), func.count(GitOperateLog.gol_id)) \
        .filter(GitOperateLog.gol_jira_project == project_name) \
        .filter(GitOperateLog.gol_operate_code == GitOperateLogModel.GOL_OPERATE_CODE_MASTER_MERGE) \
        .filter(GitOperateLog.gol_datetime >= start_date) \
        .group_by(func.date_format(GitOperateLog.gol_datetime, '%Y-%m-%d')) \
        .all()
    items = {i[0]: i[1] for i in items}

　　

将时间格式化方法

func.date_format(Table.create_time, "%Y-%m-%d %H:%i:%s")

将时间戳转化成时间

func.from_unixtime((Table.timestamp), "%Y-%m-%d %H:%i:%s")