CODEWITHSUNDEEP
sqlsqlite
dror
dror

Reputation: 3946

sqlite query for grouped time-frame

I'm trying to query my connections history table into hourly time-frame, every hour it's connection duration.

create table sessionHistory (
  _id       integer PRIMARY KEY autoincrement,
  modified  integer,
  ip        text,
  ...
  started   integer,
  ended     integer
);

Every connection start has a "started" date, and when it's finished I put an "end" date, both in unix epoch.

I'm able to group them using the following query:

select strftime('%H', started, 'unixepoch') HOUR_, 
       sum(ended-started) TOTAL_TIME_ 
from sessionHistory 
where ip='123.123.123.123' and ended!=0
group by strftime('%H', started, 'unixepoch')

Now, I wish also to include in every hour the session time that's still ongoing (ended=0).

For instance, in hour 1 the connection had started and finished with duration of 35 seconds. In hour 2 the connection had started just before turning into hour 3, with 10 seconds to turning, and stopped 10 seconds after turning into hour 3.

This means that the query needs to return something like hour 1 => 35, hour 2 => 10, hour 3 => 10. Moreover, if the connection is still up (ended=0) I want to have the current hour to hold data for "now"-started, which will accumulate in the relative hour.

Joining 2 sqlite queries will not answer the last specification, as it will always return "now"-"started", even if the session is over 1 hour long.

Upvotes: 1

Views: 696

Answers (1)

jsj
jsj

Reputation: 9381

select strftime('%H', started, 'unixepoch') HOUR_, 
       sum(ended-started)+
             (
             SELECT sum(now-started)
             from sessionHistory 
             where ip='123.123.123.123' and ended=0
             group by strftime('%H', started, 'unixepoch')
             ) TOTAL_TIME_ 
from sessionHistory 
where ip='123.123.123.123' and ended!=0
group by strftime('%H', started, 'unixepoch')

Upvotes: 4

Related Questions