[SOLVED] Query to iterate over date range and return any dates with no records

Query to iterate over date range and return any dates with no records

I am looking to write a query, by iterating over a date range, and for any dates that have no records, to return those dates.

The first thing that came to mind is to use limit 1 to make the query more efficient, as there is quite a lot of data on each date.

However, I am still unsure how to write the entire query. Here it is for one day:

SELECT *
from some_table
where parser_code ='pc' and date = '20170412' limit 1;

Any help would be much appreciated!

A short snippet of the table:

id        date        parser_code        filename     
10     2016-01-26          ccc          GENERIC.h5
1002   2016-01-26          cwgf         NON_CC.h5
349    2018-04-23          fsdgf        SOME_C.h5
3498   2019-04-22          fff          CALC.h5
4238   2024-01-26          paggg        JOME.h5
...

The date range is between 2016 and 2025.

But if the above example was all the data, and say we ran the query date form 2016-01-01 to 2019-12-30, I would expect every date to be returned that is not 2016-01-26, 2018-04-23, 2019-04-22, 2024-01-26.

Solution

You can use generate_series to generate a list of dates, then use where not exists to filter only to the ones that have no match

select
  g.value::date as date
from generate_series('2016-01-01'::timestamp, '2025-12-31'::timestamp, '1 day'::interval) as g(value)
where not exists (select 1
    from sometable st
    where st.parser_code ='pc'
      and st.date = g.value::date
);

In some circumstances it may be more efficient to just aggregate the whole table

select
  g.value::date as date
from generate_series('2016-01-01'::timestamp, '2025-12-31'::timestamp, '1 day'::interval) as g(value)
left join sometable st
  on st.parser_code ='pc'
 and st.date = g.value::date
group by
  g.value::date
having count(st.id) = 0;