Using date range with multiple columns

Question

I have a mysql query prepared to search by date range:

select * from active_listings 
where open_date >= '$start_date' 
  AND open_date <='$end_date'

which works excellent.

I have two columns in mysql: open_date and next_open_date. How can I do a range clause which includes the open_date and next_open_date.

I tried doing:

select * from active_listings 
where (open_date,next_open_date) >= '$start_date' 
  AND (open_date,next_open_date) <='$end_date')

I get no results.

Could somebody please guide me or show me the directions on how I could use multiple columns for date range.

Thanks!

score 2 · Accepted Answer · answered Dec 15 '14 at 23:11

2

To avoid ambiguity, you have to separate the ranges like so:

select * 
from active_listings 
where (open_date >= '$start_date' AND open_date <='$end_date') 
  OR (next_open_date >= '$start_date' AND next_open_date <='$end_date');

Or, a bit shorter:

select * 
from active_listings 
WHERE open_date BETWEEN '$start_date' AND '$end_date'
  OR next_open_date BETWEEN '$start_date' AND '$end_date';

answered Dec 15 '14 at 23:11

Ja͢ck

170,779
38
263
309

How could I do a range to include open_date and/or next_open_date? because I have some rows that have open_date and some rows have next_open_date. I want to show all rows that have date range selected in open_date and next_open_date. – Edward Dec 15 '14 at 23:25
@Edward Hmm, just change OR to AND then? Or, do you mean that if `next_open_date` is available it should be used and otherwise only `open_date`? Perhaps an example of what you're after may be helpful :) – Ja͢ck Dec 15 '14 at 23:32
I got it working. The OR worked! Thanks @Jack! The issue was that I used the OR in the wrong section. – Edward Dec 15 '14 at 23:36

score 1 · Answer 2 · answered Dec 15 '14 at 23:17

Not exactly sure what you want... Are you trying to calculate overlapping periods or something?

Tip: You can use BETWEEN clause to replace a combination of "greater than equal AND less than equal" conditions.

SELECT * 
FROM active_listings 
WHERE 
    open_date BETWEEN '$start_date' AND '$end_date' OR
    next_open_date BETWEEN '$start_date' AND '$end_date'

Using date range with multiple columns

2 Answers2