Constructing an SQL query with time intervals

sqlite

I've a simple but large table in an SQL database which has four fields:

id    (int PK)
time  (unix timestamp, not null)
value (double, not null) 
ID_fk (integer foreign key)

Data is recorded every minute, but at a seemingly random second for subsets of sensors, each sensor has a foreign key value though.

My question is this:

  • I need to write a query that will tell me when, over all two minute intervals in the dataset, a series of sensors have a value greater than 0.9.

I was thinking for trying to create a temporary table and do it that way, simplifying the time by only storing it at minute resolution?

Any advice would be greatly received.

Best Answer

You could try using a CTE recursive function that generates a time series.

There is an answer on SO that shows how to do it:

WITH RECURSIVE dates(StartDate, EndDate) AS 
(
    VALUES('2016-10-01 00:00:00', '2016-10-01 00:00:02')
    UNION ALL
    SELECT EndDate as StartDate, date(EndDate, '+2 seconds') as EndDate
    FROM   dates
    WHERE  EndDate < '2016-10-02 00:00:00'
)
SELECT     you_table.*
FROM       your_table
CROSS JOIN dates
WHERE      value > 0.9
AND        time >= StartDate
AND        time <  EndDate;
Related Question