0

Mysql version 5.7.28

I have a db with data in the below format

Id     ign     sp    time 
1       1      25    10:00
2       1      22    10:01
3       1      0     10:02
4       1      0     10:03
5       1      0     10:04
6       1      0     10:05
7       1      0     10:06
8       1      8     10:07
9       1      25    10:08
10      1      22    10:09
11      1      0     10:10
12      1      0     10:11
13      1      0     10:12
14      1      0     10:13
15      1      8     10:14
16      1      10    10:15

I need to design a query that will give me the result based on the condition Ign =1 and sp =0 and the time difference between time where sp is consecutively 0. In this case, sp=0 between time 10:02 to 10:06 and 10:10 to 10:13.

Desired output:

Sp Start Time  Sp Stop Time
10:02          10:06 
10:10          10:13 
6
  • Mysql version 5.7.28-0
    – nXn
    Mar 19 '20 at 4:40
  • You should get MySQL version 8 - it has Analytic functions which are great for this sort of query!
    – Vérace
    Mar 19 '20 at 5:44
  • What is the desired output?
    – Rick James
    Apr 2 '20 at 0:31
  • @RickJames I need a resultant table to be Sp Start Time Sp Stop Time 10:02 10:06 10:10 10:13
    – nXn
    Apr 2 '20 at 4:30
  • Are the minutes consecutive? Or may I give you this "equivalent output: 10:01 10:01 -- 10:09 10:14 ?
    – Rick James
    Apr 2 '20 at 5:20
1
SELECT MIN(`time`) time_from,
       MAX(`time`) time_till
FROM ( SELECT *, 
              @group := @group + CASE WHEN NOT sp AND @prev THEN 1 ELSE 0 END group_num, 
              @prev:=sp
       FROM test, (SELECT @prev:=-1, @group:=0) variable
       WHERE ign = 1
       ORDER BY `time` ) temp
WHERE NOT sp
GROUP BY group_num;

fiddle

0

Here's the approach I would try...

Step 1: build a column with consecutive sequence numbers. There at least 4 ways to do that

  • Use the "times", since they seem to be consecutive
  • @variables
  • AUTO_INCREMENT (via a extra table)
  • MariaDB (which you are not using) and its nifty "sequence" engine

Add an index to that column, which I will name seq.

Step 2: Now you can align two copies of the table thus, by something like WHERE a.seq = b.seq + 1

1000  25  1001 22
1001  22  1002  0   -- Since 22 > 0 and 0 = 0, it is the start of a gap
1002   0  1003  0
...
1006   0  1007 11   -- Since 0 = 0 and 11 > 0, it is the end of a gap 
1007  11  1008 24
1008  24  1009  0   -- an edge case: start of a 1-row gap
1009   0  1010 15   -- rest of that edge case
1010  15  ...

Step 3 A suitable WHERE clause gives you just

1001  22  1002  0   -- Since 22 > 0 and 0 = 0, it is the start of a gap
1006   0  1007 11   -- Since 0 = 0 and 11 > 0, it is the end of a gap 

1008  24  1009  0   -- an edge case: start of a 1-row gap
1009   0  1010 15   -- rest of that edge case

Step 4 Now comes another tricky pass over the data to combine the pairs in order to get the gap ranges:

1002 - 1006
1009 - 1009

I'll leave that as an exercise to the reader, but it may involve building another sequence number so you can identify the rows in each pair.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.