How do I find the time periods (start and end time) by calculating the difference between two columns?

I have a table that:

server_name | online_date
DB1 | 2017-01-01
DB1 | 2017-01-02
DB1 | 2017-01-05
DB1 | 2017-01-06
DB1 | 2017-01-11

So, the time period for which the server was offline, the resultant desired table:

server_name | offline_start_date | offline_end_date
DB1 | 2017-01-03 | 2017-01-04
DB1 | 2017-01-07 | 2017-01-10

I tried the following query:

select server_name, (online_date+1) as offline_start_date, (online_date-1) offline_end_date from servers where (online_date+1) not in (select online_date from servers) and (online_date-1) not in (select online_date from servers)

I am unable to logically write out the query, the above one certainly does not work.

Answer

You can use lead():

select server_name,
       (online_date + interval 1 day) as offline_start_date,
       (next_online_date - interval 1 day) as offline_end_date
from (select s.*, 
             lead(online_date) over (partition by server_name order by online_date) as next_online_date
      from servers s
     ) s
where next_online_date <> online_date + interval 1 day