CODEWITHSUNDEEP
phpdatetimemysqldate-range
Tegan Snyder
Tegan Snyder

Reputation: 491

Date Range Query MySQL

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

Upvotes: 2

Views: 17377

Answers (5)

Tegan Snyder
Tegan Snyder

Reputation: 491

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

Upvotes: 0

RicardO
RicardO

Reputation: 1229

This works for me. select * from news where start between '$range1' and '$range2'

Upvotes: 0

micahwittman
micahwittman

Reputation: 12476

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.

Upvotes: 5

Mark Moline
Mark Moline

Reputation: 453

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

Upvotes: 0

Seva Alekseyev
Seva Alekseyev

Reputation: 61396

You have >= in both conditions.

Upvotes: 2

Related Questions