Sign up ×
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
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 at 15:06

1 Answer 1

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

Your Answer


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.