CODEWITHSUNDEEP
mysqldatetime
Christopher
Christopher

Reputation: 3469

Mysql query with range of dates

I have a table like this one:

Items    start_datatime         end_datatime
A1       2015-01-31 10:00:00    2015-02-02 20:30:00
A2       2015-02-04 16:00:00    2015-02-04 16:30:00

And i want to select items for a given datetime (ex: 2015-02-02).

So, i'm trying this query:

SELECT Items FROM table WHERE start_datatime >= 2015-02-02 00:00:00
AND end_datatime < 2015-02-03 00:00:00

In this example, i want to select item A1 when date is 2015-01-31, 2015-02-01 and 2015-02-02. And A2 when date is 2015-02-04.

Upvotes: 0

Views: 29

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If you want any records that overlap on that date, the logic would be:

SELECT Items
FROM table
WHERE start_datatime <= '2015-02-03 00:00:00' AND
      end_datatime >= '2015-02-02 00:00:00';

If you want full overlap so the entire period includes the day:

SELECT Items
FROM table
WHERE start_datatime <= '2015-02-02 00:00:00' AND
      end_datatime >= '2015-02-03 00:00:00';

And, if you only care about midnight when the day starts:

SELECT Items
FROM table
WHERE start_datatime <= '2015-02-02 00:00:00' AND
      end_datatime >= '2015-02-02 00:00:00';

Upvotes: 1

Michael Wagner
Michael Wagner

Reputation: 1038

Remove the second WHERE and it should work:

SELECT Items FROM table WHERE start_datatime >= 2015-02-02 00:00:00 AND end_datatime <= 2015-02-02 00:00:00

To improve it, i would change end_datatime <= 2015-02-02 00:00:00 to end_datatime < 2015-02-02 00:00:00, because your solution would include all Items which end at the next day.

Also consider items which start before that day and end after it. Should they be excluded? In your solution, this would happen.

Upvotes: 0

Related Questions