Menu
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!
608
How to Select rows from a range of dates with MySQL query command. If you need to select rows from a MySQL database' table in a date range, you need to use a command like this: SELECT * FROM table WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01';
declare @StartDate date = '20170401' , @EndDate date = '20170731'; ;with Months as ( select top (datediff(month, @startdate, @enddate) + 1) [Month] = dateadd(month, row_number() over (order by number) -1, @StartDate), MonthEnd = dateadd(day,-1,dateadd(month, row_number() over (order by number), @StartDate)) from master ...
To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. The unit argument can be MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR .
SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN '2000-01-01 00:00:00' AND '2002-09-18 12:00:00';
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'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
