How do I group on continuous ranges (mysql 5.7)

Question

I want to return an array of date time ranges of when a site is down.

I am using MySQL 5.7.

Table down_time

created_at           user_id down
2017-12-15 14:50:21    1       1
2017-12-21 19:19:19    1       0
2017-12-25 22:41:14    1       1
2017-12-25 22:41:17    1       0
2017-12-25 23:11:22    1       0
2017-12-25 23:11:24    1       1
2017-12-25 23:31:24    1       1

Here on down column - 0(false) represents down and 1(true) represents up. I need a view/result like this:

down                  up                     user_id 
2017-12-21 19:19:19   2017-12-25 22:41:14    1
2017-12-25 22:41:17   2017-12-25 23:11:24    1      

Hope this example fully represents my query needs - I only need the down time ranges.

If this is possible to achieve using Laravel(5.5) SQL query helper methods, that would be great(so I can easily append query selectors like ->where('user_id', Auth::user()->id)->whereBetween('created_at', [$range['from'], $range['to']]) ), but I am not being picky in this situation - a raw MySQL(5.7.19 ) query would be great as well.

Pejorative

@Vashi So as I mentioned I want to display date time ranges of when a site was down and when it went up back again. So the pattern would be - watch for the first emergence of down(0)-ignore all the ups(1) . Then watch for the first emergence of when the site is back up(1) -ignoring all the 0'os until the 1(up) and fetch the timestamps of those two rows. After that ignore all the 1 until the first 0 again.... So in the example as you see first and last rows are being ignored and then there is two downs(0) in a row, of which only the first one is being registered. Hope this isn't too confusing. – Pejorative

Got your point. – Gio

@Mkey - What is the user_id. Do you want to have your list per user_id ? – Schafer

@BerndBuffen The user_id is related to the site and in this example we have one user/site, but there can be different users/sites . So there can be different user_id's and the selector should account for this, so there should be a dynamic way to change the user_id selector . – Pejorative

I am not sure but does your version of MySQL support ROW_NUMBER() ? – Gio

Answer 1

If I understand correctly, you can do this in MySQL by doing:

select user_id, min(created_at) as ca_0, created_at_1 as ca_1
from (select t.*,
             (select min(t2.created_at)
              from t t2
              where t2.user_id = t.user_id and t2.down = 1 and
                    t2.created_at > t.created_at
             ) as created_at_1
      from t
      where t.down = 0
     ) tt
group by user_id, created_at_1;

I have no idea how to express this in Laravel.

Saviour

Just... Great! You make it look so simple @Gordon – Pejorative

@Gordon I've clarified my original post (table name = down_time ) . I wanted to add some where down_time.user_id = $user_id and BETWEEN date extra selectors to the query, but I am a bit of confused about what t and t2 represents ? t as I understand is my table name 'down_time' and t2 is ? – Pejorative

@Mkey . . . t2 is a table alias. – Saviour

@GordonLinoff table alias for t ? – Pejorative

Answer 2

Posting a complimentary answer for those who would like to have a date time range and user_id selectors implemented in their mysql query. This was basically my main goal:

 $range = request('range');
        $range_from = $range['from'];
        $range_to = $range['to'];
        $user_id = Auth::user()->id;
        return DB::select("select user_id, min(created_at) as ca_0, created_at_1 as ca_1
from (select down_time.*,
             (select min(t2.created_at)
              from down_time t2
              where t2.user_id = $user_id and t2.down = 1 and
                    t2.created_at > down_time.created_at and 
                    t2.created_at between '$range_from' and '$range_to'
             ) as created_at_1
      from down_time
      where down_time.down = 0 and down_time.user_id = $user_id and 
      down_time.created_at between '$range_from' and '$range_to'
     ) tt
group by user_id, created_at_1;");

Pejorative