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.
What is operator in Apache Airflow An Operator is conceptually a template for a predefined task, that you can just define declaratively inside your DAG. Airflow has a very extensive set of operators available. Some popular operators from core include...
How to Manage Notifications in Airbyte? There are many types of event notifications. Successful Syncs, Failed Syncs, While creating Automatic Sync updates, Action required, warning notifications Sync enabling and Disabling notifications These Notification work through Webhook Url Webhook URL we...
Data residency refers to the physical or geographic location where an organization chooses to store or process regulated data In some cases, data may need to be transferred from one location to another in order to comply with data localization...
