Overlaps in PostgreSQL

Usually, we get to pass a date between two different dates eg. To find Employees Active in a company we can say current_date between the effectivedate and end date, this will return effectivedates that match the above condition.

But what if instead of the current date we have a date range, Postgres does offer a Time/Date function called Overlaps which can check if 2 date range OVERLAPs.

I can explain further with the set of example:

Code:

With SampleTable as(
SELECT 'Sohail'    as Name, '2001-10-30'::Date as  effectivedate, '2002-10-30'::Date as enddate
Union
SELECT 'Izebhijie' as Name, '2001-05-27'::Date as  effectivedate, '2001-10-30'::Date as enddate
Union
SELECT 'Is'        as Name, '2001-10-30'::Date as  effectivedate, '2001-10-31'::Date as enddate
Union
SELECT 'A Good Boy'   as Name, '2001-10-30'::Date as  effectivedate, '2001-10-31'::Date as enddate
)
Select * from SampleTable
where 
--(DATE '2001-02-16', DATE '2001-12-21')   OVERLAPS (effectivedate, enddate)
/*4 Rows Because the above-fixed dates overlap the effective and  enddate*/
--(DATE '2001-02-16', INTERVAL '100 days') OVERLAPS (effectivedate, enddate)
/*0 Rows because the Fixed date doesn't fall between the effective and enddate*/
--(DATE '2001-10-29', DATE '2001-10-30')   OVERLAPS (effectivedate, enddate)
/*1 Row because the Fixed Date Last Date is not equal to the Effective date*/
--(DATE '2001-10-30', DATE '2001-10-30') OVERLAPS (effectivedate, enddate)
/*3 Rows because (DATE '2001-10-30', DATE '2001-10-30') is considered as one date and it should overlap between 2 dates.


	

		

			
			
Best Open Source Business Intelligence Software Helical Insight is Here
		
		

			
		
		

			A Business Intelligence Framework
		
		

			Free
Download
		
	
	
The reason the Izebhijie Row is not picked is because both the enddates(endpoint) match which then returns false*/

Feel Free to drop a comment below if you got doubts

Thanks
Sohail Izebhijie
Helical IT Solutions Pvt Ltd