Detect multiple valid date periods for same id, interleaved with non-valid periods My table structure and some sample data (test table), as well as my desired output (results) are available here.
When I do
SELECT distinct colony, min(date_check), max(date_check)
from test
where protection ='Y'
group by colony

I only detect 1 event by colony.
Should I use a function or is there another way ?
ps : if you think of a better title to help users, don't hestitate, I struggled.
Without CTEs and window functions:
SELECT colony, MIN(date_in) date_in, date_out
FROM ( SELECT t1.colony, t1.date_check date_in, MAX(t2.date_check) date_out
       FROM test t1
       JOIN test t2 ON t1.colony = t2.colony
                   AND t1.date_check &lt; t2.date_check
                   AND t1.protection = 'Y'
                   AND t2.protection = 'Y'
                   AND NOT EXISTS ( SELECT NULL
                                    FROM test t3
                                    WHERE t1.colony = t3.colony
                                      AND t1.date_check &lt; t3.date_check
                                      AND t3.date_check &lt; t2.date_check
                                      AND t3.protection != 'Y' )
       GROUP BY t1.colony, t1.date_check ) subquery
GROUP BY colony, date_out
ORDER BY colony, date_in;

With CTE and window functions:
WITH
cte1 AS (SELECT *, 
                CASE WHEN protection = 'N' OR LAG(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'Y'
                     THEN 0
                     ELSE 1 END AS row_in,
                CASE WHEN protection = 'Y' OR LEAD(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'N'
                     THEN 0
                     ELSE 1 END AS row_out
         FROM test),
cte2 AS (SELECT *, SUM(row_in) OVER (PARTITION BY colony ORDER BY date_check) group_no
         FROM cte1)
SELECT colony, MIN(date_check) date_in, MAX(date_check) date_out
FROM cte2
WHERE protection = 'Y'
GROUP BY colony, group_no
ORDER BY colony, date_in;

https://dbfiddle.uk/?rdbms=postgres_9.5&amp;fiddle=bf769987d7211b73e89c0564bc6902a5