MySQL query to select events between start end date

Question

I have a MySQL table named  events  that contains event data  The important columns are  start  and  end  which contain string  YYYY-MM-DD  to represent when the events starts and ends   I want to get the records for all the active events in a time period   Events   ------------------------------ ID   START        END          ------------------------------ 1    2013-06-14   2013-06-14   2    2013-06-15   2013-08-21   3    2013-06-22   2013-06-25   4    2013-07-01   2013-07-10   5    2013-07-30   2013-07-31   ------------------------------  Request search   Example  All events between 2013-06-13 and 2013-07-22    1   3   4  SELECT id FROM events WHERE start BETWEEN  2013-06-13  AND  2013-07-22     1   2   3   4 SELECT id FROM events WHERE end   BETWEEN  2013-06-13  AND  2013-07-22     1   3   4       intersect    1   3   4  Example  All events between 2013-06-14 and 2013-06-14     SELECT id FROM events WHERE start BETWEEN  2013-06-14  AND  2013-06-14     1 SELECT id FROM events WHERE end   BETWEEN  2013-06-14  AND  2013-06-14     1       intersect    1  I tried many queries still I fail to get the exact SQL query   Don t you know how to do that  Any suggestions   Thanks

User · Accepted Answer

If I understood correctly you are trying to use a single query, i think you can just merge your date search toghter in WHERE clauses

SELECT id 
FROM events 
WHERE start BETWEEN '2013-06-13' AND '2013-07-22' 
AND end BETWEEN '2013-06-13' AND '2013-07-22'

or even more simply you can just use both column to set search time filter

SELECT id 
FROM events 
WHERE start >= '2013-07-22' AND end <= '2013-06-13'

User · Answer

EDIT  I ve squeezed the filter a lot  I couldn t wrap my head around it before how to make sure something really fit within the time period  It s this  Start date BEFORE the END of the time period  and End date AFTER the BEGINNING of the time period  With the help of someone in my office I think we ve figured out how to include everyone in the filter   There are 5 scenarios where a student would be deemed active during the time period in question    1  Student started and ended during the time period   2  Student started before and ended during the time period   3  Student started before and ended after the time period   4  Student started during the time period and ended after the time period   5  Student started during the time period and is still active  Doesn t have an end date yet   Given these criteria  we can actually condense the statements into a few groups because a student can only end between the period dates  after the period date  or they don t have an end date   1  Student ends during the time period AND  Student starts before OR during   2  Student ends after the time period AND  Student starts before OR during   3  Student hasn t finished yet AND  Student starts before OR during                           student programs END DATE   gt     07 01 2017 00 0 0           OR          student programs END DATE  Is Null                     AND         student programs START DATE   lt     06 30 2018 23 59 59         I think this finally covers all the bases and includes all scenarios where a student  or event  or anything is active during a time period when you only have start date and end date  Please  do not hesitate to tell me that I am missing something  I want this to be perfect so others can use this  as I don t believe the other answers have gotten everything right yet

User · Answer

select   from tbl where  endDate gt   starDate and startDate lt   endDate  attached the diagram for explanation storing simple data in DB StartDate  10 01 2020 and endDate 20 01 2020  user can provide  startDate d1  and  endDate d2  to search  here 6 scenarios can happen depicted by the 4 red lines  match data  2 green lines  no match data   so by conclusion from the image  to get data from DB by providing d1 d2  ED must be greater than d1  startDate  and SD must be less than d2  endDate

User · Answer

SELECT id FROM events WHERE start  lt    2013-07-22  AND end  gt    2013-06-13     Or use MIN   and MAX   if you don t know the precedence

User · Answer

If you would like to use INTERSECT option  the SQL is as follows   SELECT id FROM events WHERE start BETWEEN  2013-06-13  AND  2013-07-22    INTERSECT  SELECT id FROM events WHERE end BETWEEN  2013-06-13  AND  2013-07-22

User · Answer

I am assuming that active events in a time period means at least one day of the event falls inside the time period  This is a simple  find overlapping dates  problem and there is a generic solution   --   d1   d2  is the date range to check against SELECT   FROM events WHERE  d2  gt   start AND end  gt    d1   Some tests   -- list of events SELECT   FROM events   ------ ------------ ------------    id     start        end           ------ ------------ ------------       1   2013-06-14   2013-06-14        2   2013-06-15   2013-08-21        3   2013-06-22   2013-06-25        4   2013-07-01   2013-07-10        5   2013-07-30   2013-07-31    ------ ------------ ------------   -- events between  2013-06-01  2013-06-15  SELECT   FROM events WHERE  2013-06-15   gt   start AND end  gt    2013-06-01    ------ ------------ ------------    id     start        end           ------ ------------ ------------       1   2013-06-14   2013-06-14        2   2013-06-15   2013-08-21    ------ ------------ ------------   -- events between  2013-06-16  2013-06-30  SELECT   FROM events WHERE  2013-06-30   gt   start AND end  gt    2013-06-16    ------ ------------ ------------    id     start        end           ------ ------------ ------------       2   2013-06-15   2013-08-21        3   2013-06-22   2013-06-25    ------ ------------ ------------   -- events between  2013-07-01  2013-07-01  SELECT   FROM events WHERE  2013-07-01   gt   start AND end  gt    2013-07-01    ------ ------------ ------------    id     start        end           ------ ------------ ------------       2   2013-06-15   2013-08-21        4   2013-07-01   2013-07-10    ------ ------------ ------------   -- events between  2013-07-11  2013-07-29  SELECT   FROM events WHERE  2013-07-29   gt   start AND end  gt    2013-07-11    ------ ------------ ------------    id     start        end           ------ ------------ ------------       2   2013-06-15   2013-08-21    ------ ------------ ------------

User · Answer

try this      SELECT id FROM events WHERE start BETWEEN  2013-06-13  AND  2013-07-22                             AND   end   BETWEEN  2013-06-13  AND  2013-07-22    DEMO HERE  output     ID  1  3  4

User · Answer

SELECT    FROM events  WHERE start  lt    2013-07-22  OR end  gt    2013-06-13

User · Answer

You need the events that start and end within the scope  But that s not all  you also want the events that start within the scope and the events that end within the scope  But then you re still not there because you also want the events that start before the scope and end after the scope   Simplified    events with a start date in the scope  events with an end date in the scope  events with the scope startdate between the startdate and enddate   Because point 2 results in records that also meet the query in point 3 we will only need points 1 and 3  So the SQL becomes   SELECT   FROM events  WHERE start BETWEEN  2014-09-01  AND  2014-10-13   OR  2014-09-01  BETWEEN start AND end

User · Answer

Here lot of good answer but i think this will help someone   select id  from campaign where   NOW   BETWEEN start date AND end date

User · Answer

In PHP and phpMyAdmin  tb   tableDataName    Table name  now   date  Y-m-d      Current date    start and end is the fields of tabla with date format value  yyyy-m-d    query    quot SELECT   FROM  tb WHERE start  lt     quot   now  quot   AND end  gt     quot   now  quot   quot

[mysql] MySQL query to select events between start/end date

Examples related to mysql

Examples related to sql

Examples related to date

Examples related to date-range