PostgreSQL - time series interpolation

Question

I have a table "performances" with columns "date" and "count". However, the rows are sparse i.e. there are many days for which there is no row, which implicitly means that the count = 0. Is there a query I can do that when run on this:

date       count
2016-7-15  3
2016-7-12  1
2016-7-11  2

Would give me this:

date       count
2016-7-15  3
2016-7-14  0
2016-7-13  0
2016-7-12  1
2016-7-11  2

?

Check out the docs - specifically generate_series(): postgresql.org/docs/current/static/functions-srf.html — Nick
– Nick, Commented Jul 14, 2016 at 23:43

Gordon Linoff · Accepted Answer · 2016-07-14 23:45:12Z

2

You can use generate_series() and a left join:

with q as (<your query here>)
select s.dte, coalesce(q.count, 0) as count
from (select generate_series(min(q.date), max(q.date), interval '1 day') as dte
      from q
     ) s left join
     q
     on s.dte = q.date;

answered Jul 14, 2016 at 23:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - time series interpolation

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