How to merge consecutive rows of date and time using MySQL

user_3297361 0
Viewed 669

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 |
+-------+-------------+-------------+
gaps-and-islands window-functions datetime mysql sql
3 Answers

You can use window functions. The idea is to create groups of adjacent records using a window sum that increments every time the difference between two consecutive records is more than 30 minutes.

select data, min(dt) start_dt,max(dt) end_dt, count(*) cnt
from (
    select
        t.*,
        sum(lag_dt is not null and dt > lag_dt + interval 30 minute)
            over(partition by data order by dt) grp
    from (
        select
            t.*,
            lag(dt) over(partition by data order by dt) lag_dt
        from (
            select t.*, concat(date, ' ', time) dt 
            from mytable t
        ) t
    ) t
) t
group by data, grp
order by min(dt)

Note that storing date and time in two separate columns is not a good practice; I added an extra level of nesting to generate datetime values.

We can eliminate one level of nesting with date arithmetics (here, the interval must be exactly 30 minutes):

select data, min(dt) start_dt,max(dt) end_dt, count(*) cnt
from (
    select 
        t.*,
        row_number() over(partition by data order by dt) rn
    from (
        select t.*, concat(date, ' ', time) dt 
        from mytable t
    ) t
) t
group by data, dt - interval (rn * 30) minute
order by min(dt)
user_106767160

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
user_104988280

Updated for postgres

select Data, Date, Time
from (
  select *,
      Date+ Time - interval '00:30' <= 
      lag(Date+ Time,1) over (partition by Data order by Date+Time) flag
  from tablename  
) T
where flag is null or  flag is false
order by Data, Date, Time

Demo

user_141331040
Related