MySQL Forums
Forum List  »  Views

Help with query strategy for view on table
Posted by: Brian Dieckman
Date: January 15, 2019 04:36PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Help with query strategy for view on table
606
January 15, 2019 04:36PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.