2

I have a price_table, that has two columns: start_date and end_date (for promotional prices).

I'm trying to create a query that will check whether or not the given date range doesn't fall under another one that already exists.

I've been trying to use this query:

SELECT *
FROM tbl_preco
WHERE isActive = 1 AND (start_date between '2014-12-11 15:45:00' AND '2014-12-13 11:45:00'
        OR end_date between '2014-12-11 15:45:00' AND '2014-12-13 11:45:00')

The problem is:

There's a promotional price from 2014-12-10 15:30:00 to 2014-12-13 14:30:00, so neither of both BETWEEN instructions are catching it, even though the given range is in fact inside the range in the DB.

                   |------GIVEN RANGE-------|
       |-------- RANGE IN DB --------|

The example above should be returned as TRUE, and the application will tell the user that there's already a promotional price within the given range.

Leandro Dimitrio
  • 110
  • 1
  • 12
  • but 2014-12-10 15:30:00 isn't between '2014-12-11 15:45:00' AND '2014-12-13 11:45:00' and 2014-12-13 14:30:00 isn't between '2014-12-11 15:45:00' AND '2014-12-13 11:45:00' so never will return a value, I don't see a trouble with that – BredeBS Dec 11 '14 at 18:55
  • @BredeBasualdoSerraino, of course, and that's precisely what I also said. The problem is that both ranges collide. Even though the start and end dates aren't between the given values, the ranges are taking place at the same time, and this should not happen. – Leandro Dimitrio Dec 11 '14 at 19:02
  • I think you also need to detect when one of the date ranges is included completely into the other one. – axiac Dec 11 '14 at 19:06
  • 1
    with the edit is more clear, do what @Chuck says below, I think is the best answer – BredeBS Dec 11 '14 at 19:07
  • http://stackoverflow.com/q/325933/1281385 – exussum Dec 11 '14 at 19:12

2 Answers2

6

A simple condition to find out if two segments [a, b] and [c, d] intersect each other is (a-d)*(b-c) <= 0. It covers all the situations, when one date range (segment) only starts or only ends during the other and also when one of the is completely included into the other.

In order to implement this in MySQL using dates you need to convert them to numbers (Unix timestamps):

SELECT *
FROM tbl_preco
WHERE isActive = 1
   AND (UNIX_TIMESTAMP(start_date) - UNIX_TIMESTAMP('2014-12-11 15:45:00'))
     * (UNIX_TIMESTAMP(end_date) - UNIX_TIMESTAMP('2014-12-13 11:45:00')) <= 0
;

When the product is 0 the date ranges either one is included into the other (start together or end together) or one of them starts exactly when the other ends.

axiac
  • 68,258
  • 9
  • 99
  • 134
  • This is brilliant. I found another way of doing it here: https://stackoverflow.com/a/325964/2157236 – Carl Jun 01 '22 at 10:43
  • 1
    @Carl, this alternate approach is actually just the equivalent mathematical expression for the logical expression in my (linked) answer. When two real numbers have the opposite sign, their product is negative, when they have the same sign, the product is positive. – Charles Bretana Jun 01 '22 at 13:15
4

It sounds like you want any promotional sale that is active during the range. In that case, you don't need to check that the start dates and end dates exist in the range, which is what you are doing now. You simply need to check that the start date happened before the range is closed, and then that the end date happened after the range opened. Hope that makes sense?

SELECT *
FROM tbl_preco
WHERE isActive = 1 
AND start_date < '2014-12-13 11:45:00' 
AND end_date > '2014-12-11 15:45:00';
Jay Blanchard
  • 34,243
  • 16
  • 77
  • 119
Chuck
  • 196
  • 1
  • 6