way to check if two intervals overlap in amazon Athena / Presto

Question

I am wondering if we have a way to check if two dates overlap in amazon athena (when writing an athena query) . I can do this in R / Python using the int_overlaps, and interval function. e.g below int_overlaps(interval(LeadStart, LeadEnd), interval(MinStartDate, MaxEndDate)))

Min, Max, Lead are just standard R functions applied to a column in a dataframee.g Minstartdate <- Min(startdate)

I would like to repeat the same process in amazon athena where I have two date intervals, and a check to see if they overlap, if they do I would like a boolean result of 1 or 0 in a new column (like mutate function in R that puts a 1 or 0 if the dates overlap or not)

Thank you Haris

Answer 1

Assuming that LeadStart <= LeadEnd and MinStartDate <= MaxEndDate, SQL expression checking that the dates overlap would be:

NOT (LeadEnd < MinStartDate OR MaxEndDate < LeadStart)

i.e. in English: NOT (first ended before second started OR second ended before first started).

Equivalent expression (whichever you prefer):

LeadEnd >= MinStartDate AND MaxEndDate >= LeadStart

Answer 2

If you have two time intervals and corresponding start and end times for time interval (1) and time interval (2) and you would like to check whether or not there is an overlap between them (in Athena Presto):

with t0 as (
select *
from (
values
     ('02:00:00', '08:00:00', '01:00:00', '03:00:00'
      ), 
     ('02:00:00', '08:00:00', '03:00:00', '05:00:00'
      ), 
     ('02:00:00', '08:00:00', '05:00:00', '10:00:00'
      ), 
     ('02:00:00', '08:00:00', '10:00:00', '15:00:00'
      ), 
     ('02:00:00', '08:00:00', '01:00:00', '10:00:00'
      ), 
     ('02:00:00', '08:00:00', '00:00:00', '01:00:00'
      )
) AS t0 (start_time_1, end_time_1, start_time_2, end_time_2)
)

select * , 
case when (start_time_2 <= start_time_1 and end_time_2 >= start_time_1) 
     then 1
     when (start_time_2 >= start_time_1 and start_time_2 <= end_time_1) 
     then 1
     else 0 
     end as time_overlap
from t0