Search code examples
mysqlsqldatetimewindow-functionsgaps-and-islands

How to merge consecutive rows of date and time using MySQL


I have a following table (view)

+-------+-------------+-------------+
| Data  |    Date     |    Time     |
+-------+-------------+-------------+
| Data1 |  2020-08-19 |    13:00:00 |
| Data1 |  2020-08-19 |    13:30:00 |
| Data1 |  2020-08-19 |    14:00:00 |
| Data1 |  2020-08-21 |    07:00:00 |
| Data1 |  2020-08-21 |    07:30:00 |
| Data2 |  2020-08-20 |    08:00:00 |
| Data2 |  2020-08-20 |    08:30:00 |
+-------+-------------+-------------+

I am trying to find out a way to create an SQL statement that would do the following: merge data with consecutive date and time (30 mins interval)

From my basic to early intermediate competence in SQL, I fail to use correctly the GROUP BY. Looking forward for answers or directions. Many thanks again

The result should be:

+-------+-------------+-------------+
| Data  |     Date    |    Time     |
+-------+-------------+-------------+
| Data1 |  2020-08-19 |    13:00:00 |
| Data1 |  2020-08-21 |    07:00:00 |
| Data2 |  2020-08-20 |    08:00:00 |
+-------+-------------+-------------+

Solution

  • With LAG() window function:

    select Data, Date, Time
    from (
      select *,
          concat(Date, ' ', Time) - interval 30 minute <= 
          lag(concat(Date, ' ', Time)) over (partition by Data order by Date, Time) flag
      from tablename  
    ) t
    where coalesce(flag, 0) = 0
    order by Data, Date, Time
    

    See the demo.
    Results:

    > Data  | Date       | Time    
    > :---- | :--------- | :-------
    > Data1 | 2020-08-19 | 13:00:00
    > Data1 | 2020-08-21 | 07:00:00
    > Data2 | 2020-08-20 | 08:00:00