Search code examples
mysqlsqldatetimewhere-clause

MySql check if the dates range overlaps

+----+------------+---------------------+---------------------+---------+----------+
| 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.

Solution

  • 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.