Remove an element in array based on condition in PostgreSQL

Question

Using version 12.

I have a query that returns an array of dates. I'm trying to get a query that returns the array but removes the first and last dates and any date that is a Tuesday or Thursday.

Here's what it looks like:

select my_dates from my_table

{'2019-11-01','2019-11-02','2019-11-03','2019-11-04','2019-11-05','2019-11-06','2019-11-07','2019-11-08','2019-11-09','2019-11-10','2019-11-11','2019-11-12','2019-11-13','2019-11-14','2019-11-15','2019-11-16','2019-11-17','2019-11-18'}
{'2019-10-09','2019-10-10','2019-10-11'}
{'2019-11-04','2019-11-05','2019-11-06','2019-11-07','2019-11-08','2019-11-09','2019-11-10','2019-11-11','2019-11-12','2019-11-13','2019-11-14','2019-11-15','2019-11-16','2019-11-17','2019-11-18','2019-11-19','2019-11-20','2019-11-21','2019-11-22','2019-11-23','2019-11-24','2019-11-25'}
{'2020-02-06','2020-02-07','2020-02-08','2020-02-09','2020-02-10','2020-02-11','2020-02-12','2020-02-13','2020-02-14','2020-02-15','2020-02-16','2020-02-17','2020-02-18','2020-02-19','2020-02-20','2020-02-21','2020-02-22','2020-02-23','2020-02-24','2020-02-25','2020-02-26','2020-02-27','2020-02-28','2020-02-29','2020-03-01','2020-03-02','2020-03-03'}
{'2020-01-30','2020-01-31','2020-02-01','2020-02-02','2020-02-03','2020-02-04','2020-02-05','2020-02-06','2020-02-07','2020-02-08','2020-02-09','2020-02-10','2020-02-11','2020-02-12','2020-02-13','2020-02-14','2020-02-15','2020-02-16','2020-02-17','2020-02-18','2020-02-19','2020-02-20','2020-02-21','2020-02-22','2020-02-23','2020-02-24','2020-02-25'}

So the query I'm looking for should return the following for the first row:

{'2019-11-02','2019-11-03','2019-11-04','2019-11-06','2019-11-08','2019-11-09','2019-11-10','2019-11-11','2019-11-13','2019-11-15','2019-11-16','2019-11-17'}

where it removed the following date elements from it:

'2019-11-01' -- first element
'2019-11-18' -- last element

'2019-11-05' -- tuesday
'2019-11-07' -- thursday
'2019-11-12' -- tuesday
'2019-11-14'-- thursday

Gordon Linoff · Accepted Answer · 2021-04-11 13:18:48Z

1

You an unnest and re-aggregate. The dates seem to be ordered, so:

select (select array_agg(dte order by dte)
        from (select dte, min(dte) over () as min_dte,
                     max(dte) over () as max_dte
              from unnest(my_dates) dte
             ) dte
        where dte not in (min_dte, max_dte) and
              extract(dow from dte) not in (2, 4)
       ) as new_my_dates
from my_table;

Note: If the dates are not ordered and you really want the first and last elements removed, then you can use unnest() with ordinality.

answered Apr 11, 2021 at 13:18

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

Remove an element in array based on condition in PostgreSQL

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