SQL Query: how to define “Visible for N hours” if two DateTime timestamps are set?

Question

I am writing a statistics-based application from a SQLite database. There is a table in which user input and output are recorded (SessionStart, SessionEnd DateTimes).

What I'm looking for is a query that can show how many hours a user has been logged in as a line graph, so 60 users were logged in between 12:00 and 1:00 AM (at any time), between 1: 00 and 2:00 AM 54 users were registered, etc.

And I want to be able to run SUM from this, so I cannot bring entries to .NET and iterate over them that way.

I came up with a rather primitive approach, a subquery for every hour of the day, however this approach turned out to be slow and slow. I need to be able to calculate this in a couple of hundred thousand records per 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 am wondering what is the best way to determine if two DateTimes have seen in a certain hour (best scenario, how many times did it cross an hour if it was registered in a few days, but not necessarily)?

The only other idea that I had was a “hour” table specific to this, and just counting the hours that the user saw at runtime, but I feel this is more of a hack than the previous SQL.

Any help would be greatly appreciated!

Answer 1

, , , , , , ?

create table hourlyUseLog (
    userID text not null,
    date float, // julian Day
    hour0 integer default 0,
    hour1 integer default 0,

etc...

    hour23 integer default 0,
);

, , ( ) .

SQLite -, - , . - julian day ( ) / , .

, , , , hourlyUseLog.

Answer 2

Sybase ( T-SQL) .

SELECT
    StartHour AS Hour, COUNT(*) AS SessionCount
FROM
    (SELECT
        CONVERT(DATETIME, '2001-01-01 ' + Hour + ':00:00') as StartHour,
        DATEADD(HH, 1, CONVERT(DATETIME, '2001-01-01 ' + Hour + ':00:00')) as EndHour
    FROM
        (SELECT '00' AS Hour UNION ALL SELECT '01' AS Hour UNION ALL
        SELECT '02' AS Hour UNION ALL SELECT '03' AS Hour UNION ALL
        SELECT '04' AS Hour UNION ALL SELECT '05' AS Hour UNION ALL
        SELECT '06' AS Hour UNION ALL SELECT '07' AS Hour UNION ALL
        SELECT '08' AS Hour UNION ALL SELECT '09' AS Hour UNION ALL
        SELECT '10' AS Hour UNION ALL SELECT '11' AS Hour UNION ALL
        SELECT '12' AS Hour UNION ALL SELECT '13' AS Hour UNION ALL
        SELECT '14' AS Hour UNION ALL SELECT '15' AS Hour UNION ALL
        SELECT '16' AS Hour UNION ALL SELECT '17' AS Hour UNION ALL
        SELECT '18' AS Hour UNION ALL SELECT '19' AS Hour UNION ALL
        SELECT '20' AS Hour UNION ALL SELECT '21' AS Hour UNION ALL
        SELECT '22' AS Hour UNION ALL SELECT '23' AS Hour) AS Hours
    ) AS T1,
    UserSession AS T2
WHERE
    -- Logged on during, logged off during
    (T2.SessionStart >= T1.StartHour AND T2.SessionEnd < T1.EndHour)
    -- Logged on before, logged off during
    OR (T2.SessionStart < T1.StartHour AND T2.SessionEnd >= StartHour AND T2.SessionEnd < T1.EndHour)
    -- Logged on during, logged off after
    OR (T2.SessionStart >= T1.StartHour AND T2.SessionStart < T1.EndHour AND T2.SessionEnd >= T1.EndHour)
    -- Logged on before, logged off after
    OR (T2.SessionStart < T1.StartHour AND T2.SessionEnd >= T1.EndHour)
GROUP BY
    T1.StartHour
ORDER BY
    T1.StartHour

, YYYY-MM-DD. , , .

Answer 3

I would go with your idea of ​​"hacking", but I do not consider it hacked, in fact - after an hour, the value will never change, so why not calculate it once and do it with it? Convolution tables are great for this and will provide consistent query time no matter how many users you track.

You can calculate them every hour or, alternatively, you can increase each hourly account when registering / logging out and avoiding the scheduled task.