PostgreSQL version: 9.5
I am trying to write a query that will check if an array of specified decades overlaps in a range. For example, I'd like to find all of the people employed at a company in the 1980's OR the 2000's. Let's say I have two date
columns: start_date
and end_date
. Here's my best attempt so far:
SELECT *
FROM date_table
WHERE int4range(EXTRACT(DECADE from start_date)::int,
EXTRACT(DECADE from end_date)::int) && (int4range(198, 200))
This query will check for anyone employed in the 1980s, 1990s, or the 2000s. I don't want to compare a range to another range; instead, it should be checking if any of the values in the array is contained in the range. How can I accomplish this? Is there another way I could be approaching this problem?
UPDATE
Thanks to the answer by joanolo, my working query is:
SELECT *
FROM date_table
WHERE int4range(EXTRACT(DECADE from start_date)::int,
EXTRACT(DECADE from end_date)::int, '[]') @> ANY(ARRAY[198, 200])
[1, 3, 4]
, I'd like to check if either 1, 3, or 4 is contained in the range of{1, 2, 3, 4, 5, 6, 7, 8}
.int4range(EXTRACT(DECADE from start_date)::int, EXTRACT(DECADE from end_date)::int, '[]')