?

Log in

Previous Entry | Next Entry

Daily histogram of mysql data

This creates a histogram to see when rows were created, grouped by day (assuming your table has a created_date column and dates are stored in unix time)

SELECT count(id) count, from_unixtime(created_date) date FROM blah WHERE created_date > UNIX_TIMESTAMP('2011-03-31') GROUP BY CONCAT(DAY(from_unixtime(created_date))) order by created_date;

The output looks like this:

+------------+---------------------+
|      count | date                |
+------------+---------------------+
|         41 | 2011-03-31 00:08:57 |
|         25 | 2011-04-01 00:20:17 |
|         47 | 2011-04-02 00:15:41 |
|         42 | 2011-04-03 00:47:35 |
|         44 | 2011-04-04 00:04:46 |
|         48 | 2011-04-05 00:29:00 |
|         34 | 2011-04-06 00:57:42 |
|         28 | 2011-04-07 00:33:37 |
|         47 | 2011-04-08 00:19:12 |
|         30 | 2011-04-09 03:09:18 |
|         36 | 2011-04-10 00:15:38 |
|         30 | 2011-04-11 01:11:13 |
|         29 | 2011-04-12 00:01:16 |
|         30 | 2011-04-13 00:03:32 |
|         33 | 2011-04-14 00:10:06 |
|         24 | 2011-04-15 00:37:03 |
|         30 | 2011-04-16 01:53:08 |
|         32 | 2011-04-17 00:22:29 |
|         25 | 2011-04-18 00:04:15 |
|         35 | 2011-04-19 01:47:20 |
|         30 | 2011-04-20 00:56:42 |
|         43 | 2011-04-21 01:59:54 |
|         37 | 2011-04-22 00:13:42 |
|         30 | 2011-04-23 00:13:17 |
|         29 | 2011-04-24 05:36:49 |
|         28 | 2011-04-25 04:16:59 |
|         14 | 2011-04-26 00:10:00 |
+------------+---------------------+

I couldn't find how to do this anywhere. Hopefully this helps others.

Tags:

Comments

( 2 comments — Leave a comment )
patrickod
Apr. 27th, 2011 12:01 am (UTC)
Hi Joe, Here's how I usually do it: (add a from_unixtime if needed)
select date(created_at) as d, count(*) from blog_posts where created_at > '2011-04-01' group by d;
+------------+----------+
| d          | count(*) |
+------------+----------+
| 2011-04-01 |      274 | 
| 2011-04-02 |      191 | 
| 2011-04-03 |      182 | 
| 2011-04-04 |      251 | 
| 2011-04-05 |      241 | 
| 2011-04-06 |      245 | 
+------------+----------+

or if you want to group by month:

select year(created_at) as y, month(created_at) as m, count(*) from blog_posts where created_at > '2011-01-01'  group by y, m;
+------+------+----------+
| y    | m    | count(*) |
+------+------+----------+
| 2011 |    1 |     5383 | 
| 2011 |    2 |     5515 | 
| 2011 |    3 |     6626 | 
| 2011 |    4 |     5743 | 
+------+------+----------+


bostonsteamer
Apr. 27th, 2011 05:10 am (UTC)
Those are cleaner, thanks!
( 2 comments — Leave a comment )

Latest Month

August 2014
S M T W T F S
     12
3456789
10111213141516
17181920212223
24252627282930
31      

Page Summary

Powered by LiveJournal.com