mysql date between period

Question

Database structure:

activities: id, description, date, time, endDate, endTime, userId

So we got an activity, it is required to have a date, the time, endDate and endTime are not required.

The problem is, if I want to get all activities of today I got:

SELECT A.*, U.*
FROM activities
JOIN users U
ON U.id = A.userId
WHERE DATE(A.date) = DATE(CURDATE())

So that will indead, get all the activities of today, but what if we got an activity that started yesterday, and the end date will be tomorrow, I still have to get the activity?

We want all the activities that will happen next weekend so than we got this query:

SELECT A.*, U.*
FROM activities
JOIN users U
ON U.id = A.userId
WHERE UNIX_TIMESTAMP(A.DATE) BETWEEN strtotime('previous saturday') 
AND (strtotime('previous monday') - 1)

But once again, what if the 'date' is on friday and will end on tuesday, and I get all the activities that are happening in the weekend, I still have to get that activity, how do I solve this?

Thanks alot for help already! :)

endDate can be null right? In that case do you assume the event will go on forever? — melihcelik, Nov 30 '11 at 11:29

melihcelik · Answer 1 · 2011-11-30T12:42:00.343

Assuming that you don't have records that have date > endDate, following should work for you:

SELECT 
  a.*,
  a.begin_date activityBeginDate,
  IF(a.end_date IS NULL, a.begin_date, a.end_date) activityEndDate
FROM activity a
WHERE
  begin_date <= '2011-01-07' AND (end_date IS NULL OR end_date >= '2011-01-06')
HAVING
  (activityBeginDate <= '2011-01-06' AND activityEndDate >= '2011-01-06')
  OR
  (activityBeginDate >= '2011-01-07')
ORDER BY activityBeginDate

For simplicity, I removed join with users table but you can add it without problem. This query assumes you are looking for events that is "active" between 2011-01-06 and 2011-01-07.

So what we do here is list all the events that started before our desired end date OR that will end after our desired start date. In this case you will list the events that will end before your desired end date. If that's a problem, you can filter them with additional conditions.

I will further try to improve this query, it may be doing overwork that can be simplified.

case 'today': $return = ' AND activityBeginDate <= DATE(CURDATE()) OR activityEndDate >= DATE(CURDATE())'; that is what I got right now, but it doesn't work (I've added the additional stuff in the SELECT query though ;)) — user1066101, Nov 30 '11 at 11:50

score 0 · Answer 2 · answered Nov 30 '11 at 12:55

SELECT A.*, U.*
FROM activities
  JOIN users U
    ON U.id = A.userId
  CROSS JOIN                                  --- example: test dates
    ( SELECT CURDATE()    AS startDate        --- between today and 
           , CURDATE() + INTERVAL 3 DAY
                          AS endDate          --- 3 days from now
    ) AS Check
WHERE (   A.endDate IS NULL
      AND A.`date` BETWEEN Check.startDate
                       AND Check.EndDate
      )
   OR (   A.`date` <= Check.EndDate
      AND Check.StartDate <= A.endDate 
      )

mysql date between period

2 Answers2