SQL Tips: Rolling windows vs hits since midnight
Hi! I'm a monkey who knows a bit about SQL and every now and then I intend to post a few tips on this blog.
Rolling windows and visitors since midnight
Do you ever use a rolling 24 hour window to monitor live hit statistics on your website? It can give an good snapshot as to how popular your site(s) currently are, but can also leave a sinking feeling if the count drops throughout the day.
Unlike page counters, rolling-window stats can go down as well as up!
As a tip to avoid depression you may instead want to display visitors since midnight; at least this count can never go down! It sounds easy enough but the answer is not that obvious.
Let's start with a recap of a basic counter over a rolling window:
SELECT COUNT(*) FROM `monkey`
WHERE `timestamp` >= DATE_SUB(NOW(), INTERVAL 1 DAY);
Where monkey is your table name and timestamp is the field in your table containing the time stamp as a DATETIME or TIMESTAMP type.
To change this to count the number of records created since midnight, we can use:
SELECT COUNT(*) FROM `monkey`
WHERE `timestamp` >= DATE(NOW());
This nesting of the NOW() function within the DATE() function might look strange to newcomers, but it's perfectly normal. It takes the current time and date - NOW() - and extracts just the date portion, ie. today's date with no time included.
When a date without time is used in a comparison with a field of type DATETIME, MySQL and almost all SQL databases will assume the time to be midnight at the start of the date provided.
We can also combine both techniques to provide a count of the number of hits yesterday plus the number of hits so far today - I call this Yesterday Plus! in my stats package:
SELECT COUNT(*) FROM `monkey`
WHERE `timestamp` >= DATE_SUB(DATE(NOW()), INTERVAL 1 DAY);