Time Series Charts with MySQL
Time series charts can give business owners a huge amount of insight into their company’s performance. A look at, say new customer signups over time can reveal a few interesting facts.
- There might be a trend associated with the data. In other words, are values going up, down or staying the same over time?
- There might be interesting amounts of variability, as some months see larger increases or decreases than other months (or hours, days, weeks, etc.).
- Does the rate of change in your data reveal anything interesting? Is there an acceleration around the holiday season or perhaps a deceleration during summer (for some goods and services).
- Finally, like most other visualizations, time series data can contain outliers that reveal something important. Perhaps your servers were down for a few days, leading to a huge drop off in new signups.
Chart.io makes it very easy to visualize your data in a time-series. Let’s demonstrate how we can create a time-series chart of new posts on Stackoverflow.com. There are two ways to do this. We can use the Chart.io UI and drag both “# of post” and the “created” datetime column to the dataset pane (as seen below).
We can then set the timeframe we’re interested in by clicking on the pull-down arrow in the X Value field, as seen below.
Let’s go with a weekly timeframe.
Voila! The same results can also be returned via a raw SQL query.
You can also go straight to the SQL editor in Chart.io to analyze your data. Below is a table that maps the various types of dates to their respective functions in MySQL.
| Granularity | Function | Example |
|---|---|---|
| Day | DATE | DATE(`users`.`date_joined`) as x |
| Week | YEARWEEK | YEARWEEK(`users`.`date_joined`) as x |
| Month | DATE_FORMAT(col, ‘%Y-%m’) | DATE_FORMAT(`users`.`date_joined`, ‘%Y-%m’) as x |
| Year | YEAR | YEAR(`users`.`date_joined`) as x |
Note for UNIXTIMESTAMP users: If you are using UNIXTIMESTAMPs instead of the MySQL DATETIME data types, you first need to wrap your columns in the FROM_UNIXTIMESTAMP conversion function.
DATE(FROM_UNIXTIMESTAMP(date_joined)) as x For more advanced date functions, you can harness the full power of SQL to achieve the desired effect. Here are a few tips.
To view your data over a certain interval, use the following pattern:
DATE_SUB(date,INTERVAL expr unit)
The date argument specifies the starting date or datetime value. expris an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a “-” for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted.
To view data starting from a particular date, you can use a WHERE clause and a comparison operator. For example:
...WHERE date.joined > ‘2011-5-15’ GROUP BY X