Neha Neha - 3 months ago 18
SQL Question

SQL query for overlapping timestamps

I have multiple rows having start time & end time columns that overlap each other.

I need to find distinct time intervals using SQL.

Sample data:

(6 -> 7)
(6.30 -> 6.45)
(8 -> 9)
(8.30 -> 9.30)


Output:

(6 -> 7)
(8 -> 9.30)

Answer

Vertica has very powerful "time series" and "conditional events" analytics functions. Your problem can be easily solved this way...

Suppose this is your start table:

SQL> select * from otest ;
        t1          |         t2          
--------------------+--------------------
2016-03-04 06:00:00 | 2016-03-04 07:00:00
2016-03-04 06:30:00 | 2016-03-04 06:45:00
2016-03-04 08:00:00 | 2016-03-04 09:00:00
2016-03-04 08:30:00 | 2016-03-04 09:30:00
(4 rows)

Where t1 is your start timestamp and t2 is your end timestamp. All you have to do is:

SQL> select 
         min(a.t1), 
         max(a.t2) 
     from ( 
         select 
            t1, 
            t2, 
            conditional_true_event ( t1 >= lag(t2) ) 
               over ( order by t1 ) as cte 
         from otest ) a  
     group by cte 
     order by 1 ;

        min         |         max         
--------------------+--------------------
2016-03-04 06:00:00 | 2016-03-04 07:00:00
2016-03-04 08:00:00 | 2016-03-04 09:30:00
(2 rows)