Help with query strategy for view on table
Hello,
I'm hoping to get some help with a query strategy for a view to make my data a little easier to read.
What I have, basically, are entries in a table that indicate an on or off state for a given event, along with a timestamp. What I'm trying to do is present the data in a single row as: event, on_timestamp, off_timestamp.
What I can't figure out is how, for a given "on" timestamp, to get the corresponding "off" timestamp.
Sample data:
SELECT * FROM `Events`
id timestamp type state host
28 2019-01-15 19:37:04.865 MOTION 1 B1L1-PI
29 2019-01-15 19:37:13.162 NGPRODUCT 1 B1L1-PI
30 2019-01-15 19:37:16.811 NGPRODUCT 0 B1L1-PI
31 2019-01-15 19:37:22.348 WASTE 1 B1L1-PI
32 2019-01-15 19:37:25.692 WASTE 0 B1L1-PI
33 2019-01-15 19:37:28.685 NGPRODUCT 1 B1L1-PI
34 2019-01-15 19:37:30.189 NGPRODUCT 0 B1L1-PI
Expected return data:
host state on_timestamp off_timestamp
B1L1-PI MOTION 2019-01-15 19:37:04.865 NULL
B1L1-PI NGPRODUCT 2019-01-15 19:37:13.162 2019-01-15 19:37:16.811
B1L1-PI WASTE 2019-01-15 19:37:22.348 2019-01-15 19:37:25.692
B1L1-PI NGPRODUCT 2019-01-15 19:37:28.685 2019-01-15 19:37:30.189
What I've tried so far is in the query below but I'm getting duplicate records for everything; clearly I'm missing something in the join.
SELECT s.host, s.type, s.timestamp AS start_time, e.timestamp AS end_time
FROM (SELECT host, type, timestamp FROM Events WHERE state = 1) s
JOIN (SELECT host, type, timestamp FROM Events WHERE state = 0) e
ON s.host = e.host
AND e.timestamp > s.timestamp
If someone could point me to a tool or doc that would help in this situation I would appreciate it!
Thanks for your time.