By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table t (id int, dt text);
insert into t values (1, '2015-W02'), (2, '2015-W08'), (3, '2015-W21');
3 rows affected
with days as
(
select dt,
extract(dow from dt) dow,
extract(week from dt) wk,
extract(year from dt) yr
from generate_series('2015-01-01'::timestamp,
'2015-12-31'::timestamp,
'1 day'::interval) dt
)
select
*
from
t
join
days
on substring(t.dt, 1, 4)::int = days.yr
and substring(t.dt, 7, 2)::int = days.wk
where
days.dow = 6; --saturdays only
| id | dt | dt | dow | wk | yr |
|---|---|---|---|---|---|
| 1 | 2015-W02 | 2015-01-10 00:00:00 | 6 | 2 | 2015 |
| 2 | 2015-W08 | 2015-02-21 00:00:00 | 6 | 8 | 2015 |
| 3 | 2015-W21 | 2015-05-23 00:00:00 | 6 | 21 | 2015 |