在两个日期之间从MYSQL数据库获取记录

Question

I have a query which retrieves the records between two dates, this works fine when the dates are in the past using the following code.

SELECT * FROM joborders WHERE closing_date <= ( NOW() - INTERVAL 1 MINUTE ) AND closing_date >= ( NOW() - INTERVAL 11520 MINUTE )

However when I want to see if there are any records in the future, by changing the - sign to plus signs like below.

SELECT * FROM joborders WHERE closing_date <= ( NOW() + INTERVAL 1 MINUTE ) AND closing_date >= ( NOW() + INTERVAL 11520 MINUTE )

I don't get any results or any errors. even though there are records within the time range. I have tried re-writing this, and changing the plus signs to greater than etc, but nothing i do makes a difference or just gives error messages.

Answer 1

The general formula for getting datetime or timestamp values in a range is this ...

 WHERE column >= starting_time
   AND column <  immediately_after_ending_time

Your second query has the starting and ending times reversed so you won't get any rows that match. You need this instead.

SELECT * 
  FROM joborders
 WHERE closing_date >= ( NOW() + INTERVAL 1 MINUTE )
   AND closing_date <  ( NOW() + INTERVAL 8 DAY )   /* 11520 minutes */