MySql check if the dates range overlaps-CodePudding

 ---- ------------ --------------------- --------------------- --------- ---------- 
| id | percentage | from_date           | to_date             | type_id | tag      |
 ---- ------------ --------------------- --------------------- --------- ---------- 
|  1 |      10.00 | 2022-04-01 00:00:01 | 2022-04-05 23:59:59 |       1 | discount |
|  2 |      10.00 | 2022-04-06 00:00:01 | 2022-04-10 23:59:59 |       1 | uplift   |
|  3 |      10.00 | 2022-04-12 00:00:01 | 2022-04-15 23:59:59 |       1 | discount |
|  4 |      10.00 | 2022-04-20 00:00:01 | 2022-04-25 23:59:59 |       1 | uplift   |
 ---- ------------ --------------------- --------------------- --------- ----------

I'm trying to create a function in php for user to create discount/uplift the prices. The user can select the from date and to date from date picker.

Now I want to restrict the user from creating discount/uplift if the selected date range falls between existing range.

Given the above table, there is a discount on the products from 2022-04-01 to 2022-04-05 and 2022-04-12 and 2022-04-15. So user can't create any discount/uplift for this range.

As above there is an uplift in the prices between 2022-04-06 to 2022-04-10 and 2022-04-20 to 2022-04-25 and user can't create any discount/uplift for this range.

SELECT * FROM `discounts` WHERE type_id = 1 AND (`from_date` <= $fromDate AND `to_date` >= $toDate);

SELECT * FROM discounts WHERE type_id = 1 AND '$fromDate' BETWEEN from_date AND to_date

SELECT * FROM discounts WHERE type_id = 1 AND '$toDate' BETWEEN from_date AND to_date

All above queries are working.

But there is a window to create discount/uplift between 2022-04-11 00:00:00 to 2022-04-11 23:59:59 and 2022-04-16 00:00:00 to 2022-04-19 23:59:59

Is there any way to check the above condition.

EDIT

My question is: How can I validate if the user input fromDate as 2022-04-16 and toDate as 2022-04-18, because it's a valid date range which does not fall under any range in the table. So user can create record to this range.

CodePudding user response：

Yo can check like this:

SELECT * 
FROM `discounts` 
WHERE type_id = 1 
  AND `from_date` <= $toDate AND `to_date` >= $fromDate;

For $fromDate = '2022-04-11 00:00:00' and $toDate = '2022-04-11 23:59:59' the query will return nothing which means that this datetime interval is available.

See the demo.

CodePudding user response：

You can insert data depending on condition. The CTE in the below query emulates a source of parameters in question

insert into tbl(id, percentage, from_date, to_date, type_id, tag )
with params as( 
   select timestamp '2022-04-16 00:00:01' fromDate, timestamp  '2022-04-18 00:00:01'  toDate
)
select 5, 11 ,fromDate, toDate, 1, 'discount'
from params p
where not exists(
  select 1 
  from tbl t
  where type_id = 1 and p.fromDate < t.to_Date  and t.from_Date < p.toDate)