How do I get a breakdown by the hour using a single query in MySql?

Question

Let's say my table looks like this:

Sessions
start_dts (datetime)
end_dts (datetime)

and the data looks like this:

start_dts             end_dts
12/25/2011 01:55:00   12/25/2011 03:30:00

I need the query results to look like this:

Date          Hour    MinutesOnline
12/25/2011    0       0
12/25/2011    1       5
12/25/2011    2       60
12/25/2011    3       30
... (every hour of the date range being queried)

Is this even possible with a single query?

check out http://stackoverflow.com/questions/3366895/group-by-month-and-year-in-mysql — Matthew, Aug 07 '12 at 22:39
@Matthew: the main issue here is to generate N rows using 2 dates as an input. And after generating the data will be ready to use without further group by's — zerkms, Aug 07 '12 at 22:43
good question. If I had mysql available, I'm intrigued enough to try and solve it — Tony Hopkinson, Aug 07 '12 at 23:48

score 1 · Accepted Answer · edited May 23 '17 at 12:04

Here is a pretty good start. This will work for any date/time range. However, it has one main prerequisite: You need to create an intervals table with a dt_hr datetime field which contains all the intervals you are scanning over.

Ex: '2011-12-25 00:00:00',
    '2011-12-25 01:00:00',
    '2011-12-25 02:00:00',
    '2011-12-25 03:00:00',
          . . .
    '2011-12-25 23:00:00'

-

SELECT DATE_FORMAT(intervals.dt_hr,'%m/%d/%Y') AS Date,
       EXTRACT(HOUR FROM intervals.dt_hr) AS Hour,
       CASE 
          WHEN intervals.dt_hr > TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts)) 
               AND intervals.dt_hr < TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
            THEN 60
          WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts)) 
               AND intervals.dt_hr < TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
            THEN 60 - EXTRACT(MINUTE FROM s2.start_dts)
          WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
               AND intervals.dt_hr > TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts)) 
            THEN EXTRACT(MINUTE FROM s2.end_dts)
          WHEN intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts)) 
               AND intervals.dt_hr = TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))
            THEN EXTRACT(MINUTE FROM s2.end_dts) - EXTRACT(MINUTE FROM s2.start_dts)
          ELSE 0
       END AS MinutesOnLine
FROM intervals
LEFT JOIN sessions s2 
  ON intervals.dt_hr >= TIMESTAMPADD(HOUR,HOUR(s2.start_dts), DATE(s2.start_dts)) 
    AND intervals.dt_hr <= TIMESTAMPADD(HOUR,HOUR(s2.end_dts), DATE(s2.end_dts))

To generate the intervals table, you could create a stored procedure which creates a temporary table with a date_hour sequence. See Get a list of dates between two dates for a way to do this.

This looks fantastic. I'll follow-up tomorrow. – E-Madd Aug 08 '12 at 04:36 — E-Madd, Aug 08 '12 at 04:36

score 0 · Answer 2 · answered Aug 07 '12 at 23:46

How do I get a breakdown by the hour using a single query in MySql?

2 Answers2