0

I have table with following data

id   start_date end_date
1    2018-01-01 2018-01-15
2    2018-01-11 2018-01-16
3    2018-01-03 2018-02-10
4    2018-01-01 2018-03-15

I want to list events based on months. If I am on 2018-01 it gives results proper but when I am in 2018-02 it does not give me entry 4 which spread between 2018-01-01 to 2018-03-15

SELECT * FROM `table` `t` 
WHERE  (start_date >='2018-01-01' AND start_date <='2018-01-31') 
OR     (end_date >= '2018-01-01'  AND   end_date <='2018-01-31')

Any help

0
1

What about using BETWEEN by taking the first and last month of the ranges:

SELECT *
FROM `table` `t`
WHERE 
  '2018-01-01' BETWEEN
     DATE_FORMAT(start_date ,'%Y-%m-01') AND
     LAST_DAY(end_date)

As this won't use indexes an alternate is to use generated columns:

ALTER TABLE `table`
ADD start_month DATE GENERATED ALWAYS AS (DATE_FORMAT(start_date ,'%Y-%m-01')),
ADD end_month DATE GENERATED ALWAYS AS (LAST_DATE(end_date)),
ADD INDEX start_end_month(start_month,end_month)

Then the query will become the following and use the start_end_month index:

SELECT *
FROM `table` `t`
WHERE 
  '2018-01-01' BETWEEN
     start_month AND end_month
1

To check to see if start_date..end_date "overlaps" '2018-01-01'..'2018-01-31', only two comparisons is needed:

WHERE end_date   >= '2018-01-01'
  AND start_date  < '2018-01-01' + INTERVAL 1 MONTH

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.