MySQL group query by time period to count the number of members

1. When using case method (not recommended)
-

The code is as follows

Copy Code

SELECT COUNT (DISTINCT user_id) User_count, Case When create_time>1395046800 and create_time<1395050400 THEN ' 17:00-18:00 ' When create_time>1395050400 and create_time<1395054000 THEN ' 18:00-19:00 ' When create_time>1395054000 and create_time<1395057600 THEN ' 19:00-20:00 ' When create_time>1395057600 and create_time<1395061200 THEN ' 20:00-21:00 ' ELSE ' Unknown ' End as ' Date ' www.111cn.net From Tb_user_online_log WHERE create_time >1395046800 and create_time<1395061200 GROUP by ' Date ' ORDER BY Create_time

The results are as follows

2. Use timestamp conversion to group

The code is as follows	Copy Code
SELECT COUNT (DISTINCT user_id) User_count, From_unixtime ( Create_time, '%y-%m-%d%h:00:00 ' ) as hours, CONCAT (From_unixtime (create_time, '%y-%m-%d%h:00 '), '-', From_unixtime (create_time, '%H ') +1, ":") as ' date ' From Tb_user_online_log GROUP by Hours ORDER BY Create_time

The results are as follows Www.111cn.net

Note: When the case is inefficient, it is not recommended when the volume of data is large, only the solution is listed here, only the parameter