CODEWITHSUNDEEP
mysql
user1963937
user1963937

Reputation: 195

Mysql statement to select entries in date range

I have a table with activities:

id (int)
day (int)
activity_date (datetime)

I add new activities this way:

insert into activities values
(null, 1, '2015-01-01 10:00:00'),
(null, 1, '2015-01-01 11:00:00'),
(null, 1, '2015-01-01 12:00:00'),
(null, 2, '2015-01-02 10:00:00'),
(null, 2, '2015-01-02 11:00:00'),
(null, 2, '2015-01-02 12:00:00');

I added 6 entries. 3 activities for day 1 and 3 activities for day 2. Now, I want to fetch all the activities that are for the day that "right now" still has to take place. Example:

Right now time is:

2015-01-01 10:50:00

As day 1 contains an entry that is "in the range" (activities at 11:00 and 12:00 has to take place) I want to select all the activities for day 1.

I tried using "between". But just doing like

select * from activities where now() between activity_date

gives sql-error.

EDIT:

Please note that a "day" can span across several days. So the entries can be:

insert into activities values
(null, 2, '2015-09-12 10:00:00'),
(null, 2, '2015-09-12 18:00:00'),
(null, 2, '2015-09-12 20:45:00'),
(null, 2, '2015-09-13 20:45:00');

As now it is

2015-09-12 22:45:00

the solution suggested by vhu doesnt work completely. His suggestion was:

SELECT * FROM activities
WHERE activity_date >= NOW() 
AND DATE(activity_date) = DATE(NOW());

Upvotes: 0

Views: 26

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Your question is a little unclear, but I assume you want something like this...

SELECT DISTINCT x.* 
           FROM activities x 
           JOIN activities y 
             ON DATE(y.activity_date) = DATE(x.activity_date) 
          WHERE DATE(y.activity_date) = DATE('2015-01-01 10:50:00') 
            AND y.activity_date > '2015-01-01 10:50:00';

Note that on indexed data, rewriting this using range comparisons will be more efficient.

Upvotes: 0

vhu
vhu

Reputation: 12788

You can do the following:

SELECT * FROM activities
 WHERE activity_date >= NOW() 
  AND DATE(activity_date) = DATE(NOW());

This will give you all activities that haven't yet occurred but are for today.

Upvotes: 1

Related Questions