How to construct an interval table in PostgreSQL?

Question

I have a table data with two fields time and value.

Table Data
| time                 | value            |
|----------------------|------------------|
|"2020-05-31 17:16:48" | 132.868607594937 |
|"2020-05-31 17:31:12" | 74.1302380952381 |
|"2020-05-31 17:45:36" | 27.9773333333333 |
|"2020-05-31 18:00:00" | NULL             |
|"2020-05-31 18:14:24" | NULL             |
|"2020-05-31 18:28:48" | NULL             |
|"2020-06-01 05:16:48" | NULL             |
|"2020-06-01 05:31:12" | NULL             |
|"2020-06-01 05:45:36" | 10.3688461538462 |
|"2020-06-01 06:00:00" | 0.5295           |
|"2020-06-01 06:14:24" | 0.516052631578947|

As you can see there are rows in the table with and without values. I'd love to build a table that would list intervals when there were and were not values coming. It would look like this:

Table: Results
| startTime            | endTime               | hasValues |
|----------------------|-----------------------|-----------|
|"2020-05-31 17:16:48" | "2020-05-31 18:00:00" | true      |
|"2020-05-31 18:00:00" | "2020-06-01 05:45:36" | false     |
|"2020-06-01 05:45:36" | "2020-06-01 06:14:24" | true      |

How can I do it? I'm using Postgres 12.

Gordon Linoff · Accepted Answer · 2020-06-02 15:48:03Z

This is basically a lag() and lead():

select d.time as startime, lead(d.time, 1, max_time) over (order by time) as endtime,
       (value is not null) as has_value
from (select d.*,
             lag(value) over (order by time) as prev_value,
             max(time) over () as max_time
      from data d
     ) d
where (prev_value is null and value is not null) or
      (prev_value is not null and value is null)

Here is a db<>fiddle.

The above works because the first row has a non-NULL value. If the first row had a NULL value, then you can use:

select d.time as startime, lead(d.time, 1, max_time) over (order by time) as endtime,
       (value is not null) as has_value
from (select d.*,
             lag(value) over (order by time) as prev_value,
             max(time) over () as max_time,
             row_number() over (order by time) as seqnum
      from data d
     ) d
where seqnum = 1 or
      (prev_value is null and value is not null) or
      (prev_value is not null and value is null)

Or more concisely as:

select d.time as startime, lead(d.time, 1, max_time) over (order by time) as endtime,
       has_value
from (select d.*, v.has_value,
             lag(v.has_value) over (order by d.time) as prev_has_value,
             max(d.time) over () as max_time
      from data d cross join lateral
           (values (d.value is not null)) v(has_value)
     ) d
where prev_has_value is null or
      (not prev_has_value and has_value) or
      (prev_has_value and not has_value);

Collectives™ on Stack Overflow

How to construct an interval table in PostgreSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related