Btukfyl

I have a string of events being logged on a 5 minute basis throughout the day in a MySQL DB. I need to identify the first event (where logid > 0) of the day as well as the last (where logid=0), but struggling to find a simple SQL solution.

A 0 will be stored in the logid field in every row starting at midnight until the first event is triggered, at which point it will change to a number > 0. Then various events will be triggered logging a number > 0 for the remainder of the day, at which point the field will once again be logged as 0 until midnight, when the process starts over again.

Is there a quick and simple way to pull the rows identifying the time when the events start, and another result showing when the events end?

CREATE TABLE logs( 

  id INT AUTO_INCREMENT, 

  date DATETIME, 

  logid INT, 

  PRIMARY KEY (id) 

) ENGINE=INNODB;

This is the test data:

id  date                logid

1   2018-11-12 01:05:00 0   

2   2018-11-12 01:10:00 0   

3   2018-11-12 01:15:00 0

4   2018-11-12 01:20:00 0   

5   2018-11-12 01:05:00 0   

…

84  2018-11-12 06:35:00 0   

85  2018-11-12 06:35:00 1   

86  2018-11-12 06:40:00 1   

87  2018-11-12 06:45:00 1   

88  2018-11-12 06:50:00 1   

…

164 2018-11-12 15:20:00 1   

165 2018-11-12 15:25:00 0   

166 2018-11-12 15:30:00 0   

167 2018-11-12 15:35:00 0

Desired Result set:

85  2018-11-12 06:35:00 1   

165 2018-11-12 15:25:00 0

I'm not concerned about logid up until the first instance where it is greater than 0. But I need to identify the first instance where logid > 0, and then the next chronological instance where logid = 0 again.

My primary attempt was to group and order on the date and logid (edit: failed attempt removed for clarity)

Here's my latest attempt

       (SELECT *

        FROM logs

        WHERE logid>0

        GROUP BY date

        ORDER BY date

        limit 1

    )UNION ALL(   

        SELECT *

        FROM logs

        WHERE logid>0

        GROUP BY date

        ORDER BY date DESC

        limit 1)

Getting closer, but not quite there. This gives me the correct first row where logid = 1, but it gives me the last row where logid = 1 (id 164) rather than the following row where logid = 0 (id=165).

Is it possible to select the penultimate row of a set if I change limit 1 to 2?
Any other pointers to keep me moving forward?

This question doesn't seem to be a problem for others, but I thought I would post the answer I came up with in case anyone runs into a similar situation in the future.

SET @v1 := (SELECT date 

        FROM logs

        WHERE logid > 0

        GROUP BY date

        ORDER BY date

        limit 1);



(SELECT *

        FROM logs

        WHERE date>@v1 and logid>0

        GROUP BY date

        ORDER BY date

        limit 1

) UNION ALL (

SELECT *

        FROM logs

        WHERE date>@v1 and logid=0

        GROUP BY date

        ORDER BY date

        limit 1

)