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
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?
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])