I came across a little SQL problem today that supplied a small 'Eureka!' moment, for me at least. I'm sure you hardcore DBA types will mock my ignorance, but hey, we weren't born knowing this stuff. I'm using MySQL here, but the priniciple here is easily translated to your DBMS of choice.
What I am trying to do is find events, or the number of events, that happen on a particular day. This should be easy, right?
Find the number of events happening per day:
SELECT DATE(start_date_time) AS dt, /* retrieve the date… */
COUNT(*) AS freq /* …and # of events for that date… */
FROM events /* …from our events list */
GROUP BY dt /* aggregate results by calendar days… */
ORDER BY dt /* …and output in chronological order */Find details of all the events happening today:
SELECT * /* retrieve everything… */
FROM events /* …from our events list… */
WHERE DATE(start_date_time) = '2011-08-08'
/* …where the event starts today… */
ORDER BY start_date_time /* …and output in chronological order */But events - and I'm talking in particular about nightlife here - can happen post-midnight and still be thought of (by the drunken masses) as the previous day. To clarify, a DJ, band or late night cinema showing kicking off at 1am on Saturday morning, is still considered by the party-goer as Friday night, right? What I need to do is get the events starting in the wee hours included in yesterday's results.
So how to do the above queries now? My Google-fu failed me; what on earth do you type in to solve this one? Thankfully, after envisaging all sorts of fugly sub-queries and JOINs, the answer was really very easy and blindingly obvious when you think about it. All we have to do is shift the timestamps backwards by an arbitary number, say four hours (4am is the new midnight!), and we can keep the same basic (read: fast) queries we had earlier.
Number of events (partygoer-friendly version):
SELECT DATE(DATE_SUB(start_date_time, INTERVAL 4 HOUR)) AS dt,
COUNT(*) AS freq
FROM events
GROUP BY dt
ORDER BY dt
/* subtract four hours, so any event starting before 4am is
counted as happening on the previous day */Today's events (partygoer-friendly version):
SELECT * FROM events WHERE DATE(DATE_SUB(start_date_time, INTERVAL 4 HOUR)) = '2011-08-08' ORDER BY start_date_time
Unless anyone knows a better way?