SQL SELECTs across non-calendar days

« Previous / Next »Filed under geekiness, during a productive start to the week, Monday, 8th August 2011.

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?

Add a comment

Sorry, comments are closed

Jon Combe