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])
Best Answer
You can use the
ANY
andALL
array element quantifiers, together with the@>
contains element range operator.If you want to know if ANY element of the array lies within your range, which is what, according to your comments, is what I interpret you want, you can use:
(in this case, this will return TRUE, because both 5 and 7 are part of the range)
This nearly translates your request (but reversing it): Does the range contain ANY of the elements of the array?
If you would like to know that they're all in, you'd use
ALL
instead ofANY
:(in this case, this will return false, because 1 and 3 are not part of the range)
You can find these and some other examples at dbfiddle here