Get start of process and end of process in a datetime range

Question

MySQL query to find start and end of process in a given date range. There can be multiple processes in the given date range. The query I've written gives the start and end correctly if there is only one process. If there are 2 processes, it gives the start date-time stamp of the 1st process and the end date-time stamp of the second process. How can I get the start time and end time stamp of both processes (the 1st process and 2nd process)?

Table contains two columns

1. date_time, datetime
2. value, bit

Table 1 sample data
date_time                   value
2020-02-19 00:10:00           0
2020-02-19 00:11:00           0
2020-02-19 00:12:00           1
2020-02-19 00:13:00           1
2020-02-19 00:14:00           1
2020-02-19 00:15:00           0
2020-02-19 00:16:00           0
2020-02-19 00:17:00           1
2020-02-19 00:18:00           1
2020-02-19 00:19:00           1
2020-02-19 00:20:00           0

Sample output
Rowno     date_time                   value
  3.     2020-02-19 00:12:00            1
  6.     2020-02-19 00:15:00            0

  8.     2020-02-19 00:17:00            1
 11.     2020-02-19 00:20:00            0

Note: When process begins value is 1 else it is 0. To identiify start time we have to get the first row with value 1 and previous value 0. Similarly for end time we have to identify last row with value 1 and next row value 0.

Query: -- Identify 1st row with value 1

    SET @row_number = 0, @result = 0;
    select @result := (a.num - 1) as prev_rec, a.num, a.date_time, a.value from (
        SELECT (@row_number:=@row_number + 1) AS num, date_time, value
        FROM table1
        where date_time >= '2020-02-19 00:00:00' and date_time <= '2020-02-25 23:59:00') as a
    where a.value = 1 
    order by a.date_time limit 1;

-- Check if value for previous rec is 0 to identify start time

    SET @row_number = 0;
    select a.num, a.date_time, a.value from (
        SELECT (@row_number:=@row_number + 1) AS num, date_time, value
        FROM table1 
        where date_time >= '2020-02-19 00:00:00' and date_time <= '2020-02-25 23:59:00') as a
    where a.num = @result 
    order by a.date_time limit 1;

Similarly I look for end time stamp

-- Identify last row with value 1

    SET @row_number = 0, @result = 0;
    select @result := (a.num + 1) as next_rec, a.num, a.date_time, a.value from (
        SELECT (@row_number:=@row_number + 1) AS num, date_time, value
        FROM table1 
        where date_time >= '2020-02-19 00:00:00' and date_time <= '2020-02-23 23:59:59') as a
    where a.value = 1 
    order by a.date_time desc limit 1;

-- Check if value for next rec is 0 to identify end time

    SET @row_number = 0;
    select a.num, a.date_time, a.value from (
        SELECT (@row_number:=@row_number + 1) AS num, date_time, value
        FROM table1 
        where date_time >= '2020-02-19 00:00:00' and date_time <= '2020-02-23 23:59:59') as a 
    where a.num = @result
    order by a.date_time limit 1;

Can I get a way to get a solution?

Please forgive me if I have left out anything relevant. I'm asking a question for the first time here.

Answer 1

To do this in mysql 8 or mariadb 10.2 or above you simply use the lag window function:

select date_time, value from (
    select date_time, value, lag(value) over (order by date_time) previous_value
    from mysterytable
) mysterytable_with_lag
where value != previous_value
order by date_time

(optionally add or value and previous_value is null to the where clause if you want to include the earliest row when its value is 1).

For earlier versions, you can emulate the lag function with variables:

select date_time, value from (
    select date_time, value, previous_value
    from (select @previous_value := null) initvars
    cross join (
        select date_time, @previous_value previous_value, @previous_value := value value
        from mysterytable
        order by date_time
    ) mysterytable_post_initvars
) mysterytable_with_lag
where value != previous_value
order by date_time