On Fri, 2018-04-06 at 11:32 +0100, hmidi slim wrote:
> Hi,
>
> I have a table availability: id (integer), product varchar(255),
> period (daterange)
>
> I want to extract dates from a given period matching a given day.
>
> E.g: for the period from 01/04/2018 - 30/04/2018 and for day = Monday
> I want to get
> 02/04/2018
> 09/04/2018
> 16/04/2018
> 23/04/2018
> 30/04/2018
>
>
> I want to make a query such as (the query doesn't work ) but I want to
> extract dates from daterange type:
>
> select * from availability
>
> where period @> '[2018-04-02, 2018-04-20]'
>
> and extract(dow from period_date) = 1
>
>
> How can I extract dates from daterange?
>
you can add generate_series to date
possibly you could use something like this (for your given matching
range):
select *
from availability
cross join lateral
(select lower(period) +
generate_series(0,upper(period)-lower(period))
) days(d)
where period @> '[2018-04-02,2018-04-20]' and
period @> d /*see below why or how to change this*/ and
extract(dow from d) = 1
note that this gives you matching dates from full availability.period
daterange, if you need only intersection of availability.period and
given range, then add such condition in where clause or alter condition
to
period * '[2018-04-02,2018-04-20]'::daterange @> d
if you need full avaliability.period dates then condition period @> d
filters out upper(period) date which is not included in canonical
version of period but generated by subquery days.
you could as well use
generate_series(0,upper(period)-lower(period)-1)
knowing that upper(period) is not included in canonical version of
period daterange (assuming it won't ever change), and it would cross one
less days row per availability row,
on the other hand, someone reading such query may not know why -1 is
there, while my original query does not rely on subtle knowledge of
daterange internals and is more readable to anyone who can read sql.
just decide yourself what is more readable
regards, mariusz jadczak
