SQL Query: How to determine "Seen during N hour" if given two DateTime time stamps?
- by efess
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!
