News Archive
PhpRiot Newsletter
Your Email Address:

More information

Generating graphs from MySQL table data

Note: This article was originally published at Planet PHP on 18 April 2012.
Planet PHP

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:

  • Day
  • Week
  • Month
  • Year

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.

Daily 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 AAA DATE(`users`.`created`) AS `date`, AAA 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)