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

?

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

　

?

1 2 3 4 5 6 7 8 9 10 11

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