Is there a way to query a table in a specific date frequency (for example business days)?

Question

Suppose we have a SQL table with entries for different dates such as

main_table:

id (PK)	date (PK)	weekday	value
1	'01-06-2021'	Tue	20
1	'02-06-2021'	Wed	20
1	'04-06-2021'	Fri	20
1	'05-06-2021'	Sat	20
1	'07-06-2021'	Mon	20
1	'08-06-2021'	Tue	20

I would like to retrieve the entries between '30-05-2021' and '07-06-2021'. But the resulting output_table table should be in a business days format such that

output_table:

id (PK)	date (PK)	weekday	value
1	'31-05-2021'	Mon	NULL
1	'01-06-2021'	Tue	20
1	'02-06-2021'	Wed	20
1	'03-06-2021'	Thu	NULL
1	'04-06-2021'	Fri	20
1	'07-06-2021'	Mon	20
1	'08-06-2021'	Tue	20

If I query the table with a simple SELECT * WHERE date>='30-05-2021' AND date <= '07-06-2021' query the following problems would emerge:

• The date '03-06-2021' (a weekday) would be missing.
• There would be no entry for '31-05-2021' (not in main_table).
• The date '05-06-2021' would be retrieved which is, however, no business day (Saturday).

Essentially my idea was to create a business day table (date_table) and subsequently use a left join of main_table on date_table:

SELECT main_table.value 
FROM date_table
LEFT JOIN main_table.id ON (date_table.weekly_dates = main_table.date AND main_table.id = 1)
WHERE date_table.weekly_dates >= '30-05-2021'
  AND date_table.weekly_dates <= '07-06-2021'
ORDER BY date_table.weekly_dates

However, is there a more elegant solution which does not require the creation of an additional table? Is there a way to directly use it in a SQL query? I also do not prefer querying the table and using a programming language to reindex the table (e.g., Python reindex).

Many thanks in advance

Answer 1

Postgres supports generate_series() which makes this pretty simple:

select i.id, gs.date, to_char(gs.date, 'Dy') as dow, m.value
from (select distinct id from main_table) i cross join
     generate_series('2021-05-30'::date, '2021-06-07'::date, interval '1 day'
                    ) gs(date) left join
     main_table m
     on m.id = i.id and m.date = gs.date
where to_char(gs.date, 'Dy') not in ('Sat', 'Sun');