Search code examples
sqlsqlitetimestampsql-timestamp

Sqlite query by timestamp and value

I have a Sqlite table with the following rows:

  • id: int PK autoincrement
  • timestamp: int value NOT NULL. Timestamp of the DB insertion
  • value: int value NOT NULL. Possible values [0-4].

I want to query the database to obtain if all the values on the database for the registers contained within the 60 seconds before the given timestamp have the same value. For instance:

id | timestamp  | value
1  | 1594575090 |   1
2  | 1594575097 |   1
3  | 1594575100 |   1
4  | 1594575141 |   2
5  | 1594575145 |   2
6  | 1594575055 |   3
7  | 1594575060 |   4

In this case, if I made the expected query for the registers contained on the 60 seconds before the register 3 (including the register 3), it should query if the value of the registers [1,2, 3] are the same, which should return 1.

On the other side, if this query was done with register 7, it will compare value of registers [4,5,6,7] and it should return 0, as this value is not the same for the three of them.

Any guesses of how can I perform this query?

Solution

  • I think that you want this:

    select count(distinct value) = 1 result
from tablename
where id <= ? 
and timestamp - (select timestamp from tablename where id = ?) <= 60;

    Replace the ? placeholder with the id that you want the results for.
    Maybe you want the absolute value of the difference of the timestamps to be less than 60, so if this is the case then change to:

    and abs(timestamp - (select timestamp from tablename where id = ?)) <= 60;

    See the demo.