Date Range Query MySQL

Question

I need a query to select data between two dates with today's date as a reference.

The database has a datetime field for "start" and a datetime field for "end".

$todays_date = date("Y-m-d H:i:s");

$q = "SELECT * FROM news WHERE `end` >= '" .  $todays_date . "' AND `start` >= '" .  $todays_date . "' ORDER BY id DESC";

The problem is the query is still pulling results where the start date is greater than today. So then i modified my query to look like this:

 $q = "SELECT * FROM news WHERE `end` >= '" .  $todays_date . "' AND `start` >= '" .  $todays_date . "' AND `start` <='" . $todays_date . "' ORDER BY id DESC";

Is this the correct way of selection data between two datetime fields that uses todays date as a limiter?

Thanks

Answer 1

You have >= in both conditions.

Answer 2

You can use the mysql function curdate() to get the date as 2010-01-05, or now() to get the full datetime format 2010-01-05 12:55:09. It depends if you want to find items that start today or start right now.

And assuming you don't have any entries that end before they begin you could rewrite the query as

select * from news where start = curdate()

It's probably a good idea to double-check your end dates too. And it seems that you're looking for a list of news articles that have already started but not yet ended, so the query looks like this

select * from news where start < now() and end > now()

You should even be able to do this, although I haven't tested it

select * from news where now() between start and end

Your mileage may vary.

--Mark

Answer 3

You can use the now timedate function in MySQL and the BETWEEN operator.

Raw SQL:

SELECT * FROM news
WHERE NOW() BETWEEN start AND end;

Note: Be mindful of the default timezone, which affects the NOW() function, used by the server providing your MySQL resource.

Answer 4

Thank everyone I ended up using:

"select * from news where start = curdate()"

I tried "SELECT * FROM news WHERE NOW() BETWEEN start AND end;" and it worked almost correctly. It didn't want to show news articles that both started and ended on the same day at the same time.

-tegan