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

micahwittman · Accepted Answer · 2010-01-05 17:59:36Z

5

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.

answered Jan 5, 2010 at 17:59

micahwittman

12.5k2 gold badges35 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Seva Alekseyev · Accepted Answer · 2010-01-05 17:43:55Z

2

You have >= in both conditions.

answered Jan 5, 2010 at 17:43

Seva Alekseyev

61.8k25 gold badges174 silver badges297 bronze badges

Comments

Mark Moline · Accepted Answer · 2010-01-05 17:59:34Z

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

RicardO · Accepted Answer · 2011-08-22 14:44:53Z

0

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

answered Aug 22, 2011 at 14:44

RicardO

1,2191 gold badge14 silver badges24 bronze badges

Comments

Thea · Accepted Answer · 2012-05-09 22:18:02Z

0

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

edited May 9, 2012 at 22:18

Thea

8,0656 gold badges30 silver badges41 bronze badges

answered Jan 5, 2010 at 18:34

Tegan Snyder

4912 gold badges8 silver badges15 bronze badges

Collectives™ on Stack Overflow

Date Range Query MySQL

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related