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
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
When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. We also get your email address to automatically create an account for you in our website. Once your account is created, you'll be logged-in to this account.
DisagreeAgree
I allow to create an account
When you login first time using a Social Login button, we collect your account public profile information shared by Social Login provider, based on your privacy settings. We also get your email address to automatically create an account for you in our website. Once your account is created, you'll be logged-in to this account.
02 Sept 2024 – Helical IT Solutions is thrilled to announce the release of Helical Insight version 5.2.1, the latest upgrade to its Open Source Business Intelligence (BI) platform. This new version delivers a powerful, cost-effective BI solution that is...
Steps to install firebird db 1. Go to google and type firebird in search box and then click on first link. License aggrement 2. Click on downloads and then install Firebird latest version(5.0.0). 3. It will navigate to the below...
This blog explains about the complete life cycle of a bug and different status of bug from the stage it was identified,fixed,retest and close. What is Defect life cycle? Defect life cycle is the life cycle of a defect or...
