sqlite count results based whitin timeline

Question

Hope someone can help with some advise i'm trying to get a SQLite query to count the occurrence of an value in a time frame for 15min interval and return distinct count

My Data looks something like this

---------DATA----------

Connection_Time         Last_Accessed               UserName   
2015-01-12 06:00:00     2015-01-12 07:00:00          JamesG    
2015-01-12 06:10:00     2015-01-12 06:12:00          JohnH
2015-01-12 06:32:00     2015-01-12 07:00:00          JohnH

This is the result I would like

Result                      count
"2015-01-12 06:00:00"         2
"2015-01-12 06:15:00"         1
"2015-01-12 06:30:00"         2
"2015-01-12 06:45:00"         2
"2015-01-12 07:00:00"         2

With my current select I get a result but it doesn't consider the timespan between Connection_Time and Last Accessed

select datetime((strftime('%s', currentUsers.Connection_Time) / 300) * 300, 'unixepoch') >    >     interval,
count(distinct(UserName)) cnt
from currentUsers
group by interval
order by interval

Any help would be appreciated.

Answer 1

To compute all the intervals, you need a CTE (this requires a recent enough SQLite version):

WITH RECURSIVE
min_time(t) AS (
  SELECT datetime((strftime('%s',
                            (SELECT MIN(Connection_Time)
                             FROM currentUsers)
                  ) / 300) * 300,
                  'unixepoch')
),
max_time(t) AS (
  SELECT datetime((strftime('%s',
                            (SELECT MAX(Last_Access)
                             FROM currentUsers)
                  ) / 300) * 300,
                  'unixepoch')
),
intervals(t) AS (
  SELECT t FROM min_time
  UNION ALL
  SELECT datetime(t, '+15 minutes')
  FROM intervals
  WHERE t <= (SELECT t FROM max_time)
)
SELECT t AS interval,
       (SELECT COUNT(DISTINCT UserName)
        FROM currentUsers
        WHERE Connection_Time < datetime(intervals.t, '+15 minutes')
          AND Last_Accessed >= intervals.t
       ) AS Count
FROM intervals;