Mysql query with range of dates

Question

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.

@MKhalidJunaid, yeah. I've just corrected the question.

Christopher
– Christopher

2015-02-02 19:36:25 +00:00
Commented Feb 2, 2015 at 19:36 — Christopher
– Christopher, Commented Feb 2, 2015 at 19:36

Gordon Linoff · Accepted Answer · 2015-02-02 20:06:25Z

1

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';

edited Feb 2, 2015 at 20:06

answered Feb 2, 2015 at 19:36

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Christopher Over a year ago

i got what i want with WHERE start_datatime <= '2015-02-02 23:59:59' AND end_datatime >= '2015-02-02 00:00:00'

Gordon Linoff Over a year ago

It is better to use start_datatime < '2015-02-03' rather than start_datatime <= '2015-02-02 23:59:59'.

Michael Wagner · Accepted Answer · 2015-02-02 19:35:47Z

0

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.

answered Feb 2, 2015 at 19:35

Michael Wagner

1,0381 gold badge8 silver badges20 bronze badges

Collectives™ on Stack Overflow

Mysql query with range of dates

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related