Hello all.

I'm writing a statistics application based off a SQLite database. There is a table which records when users Login and Logout (SessionStart, SessionEnd DateTimes).

What i'm looking for is a query that can show what hours user have been logged in, in sort of a line graph way- so between the hours of 12:00 and 1:00AM there were 60 users logged in (at any point), between the hours of 1:00 and 2:00AM there were 54 users logged in, etc...

And I want to be able to run a SUM of this, which is why I can't bring the records into .NET and iterate through them that way.

I've come up with a rather primative approach, a subquery for each hour of the day, however this approach has proved to be slow and slow. I need to be able to calculate this for a couple hundred thousand records in a split second..

  SELECT
        case
        when (strftime('%s',datetime(date(sessionstart), '+0 hours')) > strftime('%s',sessionstart)
        AND strftime('%s',datetime(date(sessionstart), '+0 hours')) < strftime('%s',sessionend))
        OR (strftime('%s',datetime(date(sessionstart), '+1 hours')) > strftime('%s',sessionstart)
        AND strftime('%s',datetime(date(sessionstart), '+1 hours')) < strftime('%s',sessionend))
        OR (strftime('%s',datetime(date(sessionstart), '+0 hours')) < strftime('%s',sessionstart)
        AND strftime('%s',datetime(date(sessionstart), '+1 hours')) > strftime('%s',sessionend))
        then 1 else 0 end as hour_zero,
... hour_one, 
... hour_two, 
........ hour_twentythree
FROM UserSession

I'm wondering what better way to determine if two DateTimes have been seen durring a particular hour (best case scenario, how many times it has crossed an hour if it was logged in multiple days, but not necessary)?

The only other idea I had is have a "hour" table specific to this, and just tally up the hours the user has been seen at runtime, but I feel like this is more of a hack than the previous SQL.

Any help would be greatly appreciated!