CODEWITHSUNDEEP
sqlsqlite
Kitson
Kitson

Reputation: 1678

How do I produce a time interval query in SQLite?

I have an events based table that I would like to produce a query, by minute for the number of events that were occuring.

For example, I have an event table like:

CREATE TABLE events (
     session_id  TEXT,
     event       TEXT,
     time_stamp  DATETIME
)

Which I have transformed into the following type of table:

CREATE TABLE sessions (
     session_id    TEXT,
     start_ts      DATETIME,
     end_ts        DATETIME,
     duration      INTEGER
);

Now I want to create a query that would group the sessions by a count of those that were active during a particular minute. Where I would essentially get back something like:

TIME_INTERVAL ACTIVE_SESSIONS
------------- ---------------
18:00         1
18:01         5
18:02         3
18:03         0
18:04         2

Upvotes: 3

Views: 5537

Answers (3)

Brett Thomas
Brett Thomas

Reputation: 1814

This isn't exactly your query, but I think it could help. Did you look into the SQLite R-Tree module? This would allow you to create a virtual index on the start/stop time: 

CREATE VIRTUAL TABLE sessions_index USING rtree (id, start, end);

Then you could search via: 

SELECT * FROM sessions_index WHERE end >= <first minute> AND start <= <last minute>;

Upvotes: 1

Kitson
Kitson

Reputation: 1678

Ok, I think I got more what I wanted. It doesn't account for intervals that are empty, but it is good enough for what I need.

select strftime('%Y-%m-%dT%H:%M:00.000',start_ts) TIME_INTERVAL, 
    (select count(session_id) 
      from sessions s2 
      where strftime('%Y-%m-%dT%H:%M:00.000',s1.start_ts) between s2.start_ts and s2.end_ts) ACTIVE_SESSIONS
   from sessions s1
   group by strftime('%Y-%m-%dT%H:%M:00.000',start_ts);

This will generate a row per minute for the period that the data covers with a count for the number of sessions that were had started (start_ts) but hadn't finished (end_ts).

Upvotes: 3

vog
vog

Reputation: 25597

PostgreSQL allows the following query.

In contrast to your example, this returns an additional column for the day, and it omits the minutes where nothing happened (count=0).

select
    day, hour, minute, count(*)
from
    (values ( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),
            (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
            (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
            (30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
            (40),(41),(42),(43),(44),(45),(46),(47),(48),(49),
            (50),(51),(52),(53),(54),(55),(56),(57),(58),(59))
     as minutes (minute),
    (values ( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),
            (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
            (20),(21),(22),(23))
     as hours (hour),
    (select distinct cast(start_ts as date) from sessions
     union
     select distinct cast(end_ts as date) from sessions)
     as days (day),
    sessions
where
    (day,hour,minute)
    between (cast(start_ts as date),extract(hour from start_ts),extract(minute from start_ts))
        and (cast(end_ts as date),  extract(hour from end_ts),  extract(minute from end_ts))
group by
    day, hour, minute
order by
    day, hour, minute;

Upvotes: 1

Related Questions