Generating graphs from MySQL table data
Graphs and charts are a useful visual way to view historical data - they make it easier to detect trends and get a big-picture view of data. All we need is timestamped data - table rows that are stamped with a specific date/time format, that can be used to group rows into time periods.
Time stamped data
In order to aggregate table data by time periods / ranges, we need a date/time column in the table we want to analyze. Appropriate types for such a column include TIMESTAMP, DATETIME and DATE, but we can also use string / numeric types for grouping data together if they contain some sort of date/time information - though those will be much less flexible than native date/time types.
I usually opt to go with the TIMESTAMP format, for a couple of reasons -
- TIMESTAMP data is stored internally as UTC timezone - this makes such data portable between servers without needing to adjust for timezones.
- TIMESTAMP type have auto-initialization and updating features - using the CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP values. As of MySQL ver. 5.6.5 you can use those properties with DATETIME as well, but since most servers and linux distributions use lower (considered more stable) versions, it's usually not an option.
The TIMESTAMP format is limited by its date range (1970-01-01 to 2038-01-19), so you might need to use DATETIME if you're using dates far in the future or past.
Grouping time-stamped rows into time periods
Assuming we have time-stamped data in our table, we can now start aggregating it into specific time periods. The most commonly used time periods for grouping are:
Lets take an example table schema and see how we group the data for each period.users - (int) `id` - (varchar) `name` - (smallint) `role` - (timestamp) `created`
A very simple users table. Our time-stamped column is obviously `created`, and we'll be using it to group our data.
Our first task is to extract daily new users data from the table. We'll do this by extracting the date portion of the timestamp using the DATE() function.SELECT A A A DATE(`users`.`created`) AS `date`, A A A COUNT(`users`.`id`) AS `count` FROM `users` WHERE `users`.`created` BETWEEN '2012-01-01 00:00:00' AND '2012-01-31 23:59:59' GROUP BY `date` ORDER BY `date`
This returns data that looks like the following:date count 2012-01-01 12 2012-01-02 12 2012-01-03 36 2012-01-04 34 2012-01-05 36 2012-01-06 29 ... ...
Note that I limited the query to a time range of 1 month (January 1st to January 31st). I used this particular format for a couple of reasons:
- It allows MySQL to use an index for filtering the result. Had I used the DATE() function in the WHERE clause (DATE(`users`.`created`) BETWEEN ...), an index could n
Truncated by Planet PHP, read more at the original (another 10919 bytes)