How to select periods of time with empty data?

Question

I want to find out all periods with empty data, given the following table my_table:

id         day       
29         2017-06-05
26         2017-06-05
30         2017-06-06
30         2017-06-06
21         2017-06-06
21         2017-07-01
29         2017-07-01
30         2017-07-20

The answer would be:

Empty_start    Empty_end
2017-06-07     2017-06-30
2017-07-02     2017-07-19

It's important that the number of months is considered. For example, in the first row the answer 2017-06-31 would be incorrect.

How can I write this query in Hive?

Answer 1

You can use lag() or lead():

select date_add(day, 1) as empty_start, date_add(next_day, -1) as empty_end
from (select day,
             lead(day) over (order by day) as next_day
      from t
      group by day
     ) t
where next_day <> date_add(day, 1);