1

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.

  • 5
    You've tagged the question with SQLite - is that what you're using? Just double checking :) – Philᵀᴹ Feb 25 '13 at 11:05
  • All two minute intervals or all two minute "whole" intervals - e.g. are you bucketing the data by interval, or do you want to see all possible intervals (e.g. any span of time that is 2 minutes (to the second?) or just a bucketed set of minutes? – N West Sep 18 '15 at 15:06
1

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;
0

For every line, you check that there are no lines in the previous 2 seconds with value smaller than 0.9 (therefore they all are greater than 0.9).

select * from table outer_table where value > 0.9 and not exists (
    select * from table where value <= 0.9 and (outer_table.time - time) <= 2
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.