Sort data from MYSQL database by hour

Question

I have users in db that I want to sort by hour and display count of users registered at that hour.

select
  date_format(create_time, '%Y-%m-%d %h%p') as date,
  count(id) as 'Number of registrations'
from users
group by 1
order by 1 desc
;

The above code will work; however, what I am trying to do is display 0's for the hours that have no user registrations. For example, if there were no registrations at 5pm, this will skip row for 5pm, which is logical. Is there a way to achieve what I am trying?

I don't see how that would give me the results I want. Do you mind explaining? Thank you! — Grigor, Sep 13 '13 at 17:45
This is very similar to: http://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range — noz, Sep 13 '13 at 17:46
@Grigore Sorry if it were clear in my head I would have written the answer.Something about joining with a SELECT where dates are null ON condition that original table.user!=user.from (select where dates are null). — Mihai, Sep 13 '13 at 17:48
Take a look at the marked duplicate. You need to join to a table that contains all the values you want to match against - both those with and lacking the representative column. — ethrbunny, Sep 13 '13 at 17:54
SQL doesn't do what you're asking without the fudgery linked by @noz, but IMHO this would be best accomplished in the program code, not the SQL query. — Sammitch, Sep 13 '13 at 17:58

score 1 · Answer 1 · answered Sep 13 '13 at 17:57

You could use a query like this:

select
  date_format(t.d + INTERVAL t.h HOUR, '%Y-%m-%d %h%p') as date,
  count(id) as 'Number of registrations'
from (
  SELECT *
  FROM
    (SELECT DISTINCT DATE(create_time) d FROM users) dates,
    (SELECT 0 h UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
    UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
    UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
    UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
    UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23) hours
  ) t LEFT JOIN users
  ON DATE(users.create_time)=t.d AND HOUR(users.create_time)=t.h
group by t.d, t.h
order by t.d, t.h

Please see fiddle here.

This is TOO slow for a db with 1000's of results. Nice method nonetheless. — Grigor, Sep 13 '13 at 18:29

score 1 · Answer 2 · answered Sep 13 '13 at 18:07

You need to generate all possible day and hour combinations.

Assuming that you have at least one record on each day and one record for each hour, you can do:

select concat(d.theday, ' ', h.thehour) as date,
       count(id) as 'Number of registrations'
from (select distinct date_format(create_time, '%Y-%m-%d') as theday from users
     ) d cross join
     (select distinct date_format(create_time, '%h%p') as thehour from users
     ) h left outer join
     users u
     on date_format(u.create_time, '%Y-%m-%d %h%p) = concat(d.theday, ' ', h.thehour)
group by concat(d.theday, ' ', h.thehour)
order by 1 desc;

Works if there are few results in db. Too slow. Thanks though! — Grigor, Sep 13 '13 at 18:29

Sort data from MYSQL database by hour

2 Answers2