CODEWITHSUNDEEP
sqlpostgresql
Vlad K
Vlad K

Reputation: 2841

How to fill time interval gaps in SELECT query?

Let's say, I have a table that contains a list of a service outages.
The fields are: service_id, from_time, to_time. The times are timestamp type.

The status of a service withing these intervals is "DOWN". Other times is "UP".

I'm looking for a query that would return a continuous list of time intervals representing a particular service health between a given date and now.

E.g. the table contains only one outage for the service srv1:

srv1, 11/01/2017 13:43:32, 11/01/2017 15:20:12, DOWN

Then, the result of the query from the beggining of the year should be looking like this:

srv1, 11/01/2017 15:20:12, 24/07/2017 23:55:00, UP  
srv1, 11/01/2017 13:43:31, 11/01/2017 15:20:12, DOWN  
srv1, 01/01/2017 00:00:00, 11/01/2017 13:43:31, UP

Assume that the outages are not overlapping.
The from_time of a following interval is equal to the to_time of a previous time interval for a service.
If a service is currently DOWN, then the to_time is equal to NULL in the outages table.

The query is for PG 9.6

Upvotes: 0

Views: 851

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

You can use the lag() function to look back at a previous row.

In this case something like:

with down as (select *, lag(to_time) over (rows unbounded preceding)
                        as last_up
                from outage order by from_time asc),
      full_log as (select host, from_time, to_time, 'down' 
                          AS as status 
                     FROM down
                UNION ALL
                   select host, last_up, from_time, 'up' as status
                     from down)
select * from full_log order by from_time asc;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Basically, you want to:

  • select all the records
  • select the "in-between" records, adding in the opposite status
  • add in the last status

The following uses union all for this purpose:

select t.*
from t
union all
select id, coalesce(prev_to_time, '2017-01-01'::timestamp) as start_time,
       start_time as to_time,
       (case when status = 'DOWN' then 'UP' else 'DOWN' end) as status
from (select t.*,
             lag(to_time) over (partition by id order by start_time) as prev_to_time,
             lag(status) over (partition by id order by start_time) as prev_status
      from t
     ) t
where (prev_to_time is null and start_time <> '2017-01-01'::timestamp or
       prev_to_time <> start_time
      ) and
      (prev_status is distinct from status)
union all
select id, max(end_time), now(), 'UP'
from t
group by id
having max(end_time) <> now()
order by id, start_time;

Upvotes: 1

Related Questions