Joe (bostonsteamer) wrote,
Joe
bostonsteamer

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: sql, work
Subscribe
  • Post a new comment

    Error

    Comments allowed for friends only

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 2 comments