In our HR application, we have an entry for leave request to apply the leave. So we have to valid any date overlapping between two leave request.

Please find the below sample examples:

case 1 -- Working as Expected

Assume that one request already saved in database and then trying insert new request with date overlapping.

Existing leave request dates: '2022-07-20' as start_date '2022-07-23' as end_date
new leave Request dates: '2022-07-20' as start_date '2022-07-20' as end_date

My attempts mentioned below:
select ('2022-07-20'::date,'2022-07-23'::date) overlaps ('2022-07-20'::date,'2022-07-20'::date)
-- returns true

case 2 -- Not Working

Existing leave request dates: '2022-07-20' as start_date '2022-07-23' as end_date
new leave Request dates: '2022-07-16' as start_date '2022-07-20' as end_date

My attempts mentioned below:
select ('2022-07-20'::date,'2022-07-23'::date) overlaps ('2022-07-16'::date,'2022-07-20'::date)
-- returns false

For better understanding PFB

//Start2 date is '2022-07-20'
select ('2022-07-20'::date,'2022-07-23'::date) 
overlaps ('2022-07-20'::date,'2022-07-20'::date) -- true (Case 1)
//Start2 date is '2022-07-16' 
select ('2022-07-20'::date,'2022-07-23'::date) 
overlaps ('2022-07-16'::date,'2022-07-20'::date) -- false(Case 2)

Could you please expain the issue with my code snipp and suggest any solution for this.

CodePudding user response：

As documented in the manual OVERLAPS uses "half-open" periods:

Each time period is considered to represent the half-open interval start <= time < end, unless start and end are equal in which case it represents that single time instant. This means for instance that two time periods with only an endpoint in common do not overlap.

^{(emphasis mine)}

If you need to include the endpoints use dateranges that include both edges:

select daterange('2022-07-20'::date,'2022-07-23'::date, '[]') 
       && daterange('2022-07-16'::date,'2022-07-20'::date, '[]')

The above yields true