Date Range Query MySQL
Solution 1
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.
Solution 2
You have >= in both conditions.
Tegan Snyder
I'm a software engineer that specializes in web application development and eCommerce implementations using PHP, MySQL, JQuery, and everything the open source community has to offer. I also LOVE Magento :)
Updated on June 04, 2022Comments
-
Tegan Snyder almost 2 years
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