Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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) and an 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, Thanks, James

share|improve this question
4  
You've tagged the question with SQLite - is that what you're using? Just double checking :) –  FreshPhilOfSO Feb 25 '13 at 11:05
add comment

1 Answer

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
)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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