Selecting MySQL records in time bands from random log data
Lets say you have thousands of log records in a database table that have been inserted at sporadic moments over a period of time.
Each database entry could have been triggered by a random event occurring during the execution of a process that you wish to monitor.
When the time comes to extract some meaning from these logs, you may be wondering how to select a subset of your records that represent snapshots of the data at more evenly spread points in time.
How?
You use “Group By” statement using time/date column wrapped in a date function such as in this MySQL example, below. It shows a daily count of the number of logs for the home page, say, within the last 3 weeks :
SELECT
count(col_page_log_id) as count,
MONTH(col_date_created) as month_of_year,
DAYOFYEAR(col_date_created) as day_of_year,
WEEK(col_date_created) as week_of_year,
YEAR(col_date_created) as year,
DATE_FORMAT(col_date_created,'%a') day_of_week,
DATE_FORMAT(col_date_created,'<strong>%a</strong> %D %b %Y') as pretty
FROM
tbl_page_logs
WHERE
col_date_created > DATE_SUB(NOW(), INTERVAL 3 WEEK)
AND
col_pg_request = '/index.htm'
GROUP BY
day_of_year,year
ORDER BY
year DESC, day_of_year DESC
(Note: I have included some extra column aliases in the SELECT statement to make it easier to see how it can be applied to a variety of time bands. E.g. the “day_of_year” column in the GROUP BY / ORDER BY statements could be replaced by “week_of_year” or “month_of_year” depending on your needs.)