Transforming start and end times to simple graph

Question

I've been struggeling with this problem for a while now.

I have a table sessions which stores the start and end times of sessions.

What I am trying to archive is a simple graph that shows the number of active sessions for the last 24 hours in a reasonable interval.

My current solution is to just query the number of active sessions every 15 minutes and store them in a seperate table with just a timestamp and a number column. This is basically duplicate data and not very exact.

The following query produces the needed result, but is very inefficient:

CREATE TEMPORARY TABLE timings (tstamp timestamp);
INSERT INTO timings (tstamp) VALUES (CURRENT_TIMESTAMP - 0 MINUTE), (CURRENT_TIMESTAMP - 15 MINUTE), [...];
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE tstamp BETWEEN starttime AND endtime) AS 'numSessions' FROM timings;

I am using MariaDB 10.1. Version 10.2 adds CTEs and updating is a possibility.

Answer 1

I've upgraded and worked a bit with CTEs. The following query runs in about half of the time.

WITH RECURSIVE tstamp_cte AS (
    SELECT 0 AS n, CURRENT_TIMESTAMP AS tstamp FROM DUAL
    UNION ALL
    SELECT n + 1, tstamp - INTERVAL 15 MINUTE FROM tstamp_cte WHERE n < 96
)
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE starttime < tstamp AND endtime >= tstamp OR endtime IS NULL) AS 'sessionCount'
FROM tstamp_cte