如何在MySQL查询中创建日期间隔？

Question

I want to make a date interval in a MySQL query. The date is extracted dynamically from an HTML Form and I want to search if something is between 2 days after and after a fixed date. I've made an example but it's not working well.

SELECT * FROM `sessions` WHERE `start_date` = '2014-05-12'
 or `start_date` between DATE_SUB(start_date, INTERVAL 1 DAY) 
and DATE_ADD(start_date, INTERVAL 1 DAY)

Any other propositions ?

Answer 1

If I'm not wrong every time this query is resulting all rows from the sessions table. Let's take a look at this particular part of the query,

start_date between DATE_SUB(start_date, INTERVAL 1 DAY) and DATE_ADD(start_date, INTERVAL 1 DAY),

So if the start_date value of a row is 22-07-2014 the query translates to, start_date between 21-07-2014 and 23-07-2014)

So every row satisfies the condition as every date x is between x - 1th day and x + 1th day.

So if you want to execute the query to find a date between a certain interval, try

start_date between DATE_SUB('$start_date', INTERVAL 1 DAY) and DATE_ADD('$start_date', INTERVAL 1 DAY)

instead. Note that '$start_date' is a php variable that is the date you are comparing the row with.