0

I have a given SQLIt database (so no chance to do it in a better way):

CREATE TABLE `history` (
    `TIMESTAMP` TIMESTAMP,
    `DEVICE`    `enter code here`varchar(32),
    `TYPE`  varchar(32),
    `EVENT` varchar(512),
    `READING`   varchar(32),
    `VALUE` varchar(32),
    `UNIT`  varchar(32)
);

In this table I have for example the following data:

DEVICE VALUE
d1     1
d5     500
d2     10
d1     2            <--
d5     501
d1     100          <---

I want to figure out for the device "d1" all timestamps where the difference between the last value and the current value is > 10

I have absolutly no idea how to do this with SQL

thank you

Peter H
  • 23
  • 2

1 Answers1

1

To get the last value for the current timestamp T, you would use a query like this:

SELECT Value
FROM History
WHERE Device = '...'
  AND Timestamp < T
ORDER BY Timestamp DESC
LIMIT 1;

You can then use this as a correlated subquery in your query:

SELECT Timestamp
FROM History
WHERE Device = 'd1'
  AND ABS((SELECT Value
           FROM History AS last
           WHERE Device = 'd1'                    -- or Device = History.Device
             AND last.Timestamp < History.Timestamp
           ORDER BY Timestamp DESC
          ) - Timestamp) > 10;
CL.
  • 173,858
  • 17
  • 217
  • 259