My query to retrieve events between 2 search dates does not return any results, why?

Question

Context

I have modelized a calendar with a table. The entries are the calendar's events:

id ; event_start_date ; event_end_date.

One entry is:

11 ; 2021-01-28 15:15:37 ; 2022-01-11 15:15:37

The script I've written accepts a search interval of dates as entries and should return all the events which are contained within the search interval (if the search interval is contained in the events intervals, or if one of the search interval dates is contained in the events intervals, or if the events intervals are contained in the search interval). I think that the (unique) case where no results should be returned is: if the search interval is completely out of the bounds of all the events.

Example of search, Expected and Actual behaviors

Consider the following search interval: search_start_date = 2021-01-26 00:00:00 and search_end_date = 2021-01-30 00:00:00.

Expected behavior: The event #11 should be returned because even though the search_start_date is out of the event's interval, the search_end_date is within it.

Actual behavior: no event is returned.

The script

SGBD used: MySQL. Language used: SQL.

select *, @search_start_date:="2021-01-26 00:00:00", @search_end_date:="2021-01-30 00:00:00" 

from `calendars_events` 

where

(@search_start_date between `event_start_date` and `event_end_date`) or (@search_end_date between `event_start_date` and `event_end_date`)

or

(`event_start_date` between @search_start_date and @search_end_date) or (`event_end_date` between @search_start_date and @search_end_date)

Question

Why is the event not returned? I think I correctly use MySQL variables and I think I can use between to make dates comparison.

Confusing... Why is there no `event_start_date` or similar`? How would you know when the event starts? And why is the search mixing searches for the record's creation timestamp and the event's time period? — sticky bit, Feb 01 '21 at 21:44
@stickybit yes I'm really sorry Sir, I've edited the question^^' — JarsOfJam-Scheduler, Feb 01 '21 at 21:48
The test for overlaps is straightforward. Event A overlaps Event B if Event A starts before Event B ends, and ends after Event B starts. — Strawberry, Feb 01 '21 at 22:42

score 1 · Accepted Answer · answered Feb 02 '21 at 03:25

Variables must be initialized in the FROM clause (MySQL query / clause execution order), and the condition can be much simpler:

SELECT e.* 
FROM `calendars_events` e, (
  SELECT
    @search_start_date:="2021-01-26 00:00:00",
    @search_end_date:="2021-01-30 00:00:00"
  ) vars
WHERE @search_start_date <= event_end_date AND @search_end_date >= event_start_date

fiddle

Note that starting MySQL 8 setting user variables within expressions is deprecated and will be removed in a future release.

Gordon Linoff · Answer 2 · 2021-02-01T22:02:48.007

MySQL is not clear about when variables are initialized. I am pretty sure that if they are initialized in the FROM clause, then that is evaluated before the other clauses. So, I would recommend:

select ce.*, @search_creation_date, @search_end_date
from calendars_events ce cross join
     (select @search_creation_date := '2021-01-26', @search_end_date := '2021-01-30' 
where (@search_creation_date between `event_creation_date` and `event_end_date`) or
      (@search_end_date between `event_creation_date` and `event_end_date`) or
      (`event_creation_date` between @search_creation_date and @search_end_date) or (`event_end_date` between @search_creation_date and @search_end_date);

You can also assign the variables in a statement before the select.

Note: I'm not sure that your where clause really does what you want. But you haven't described that. You might want to ask another question about how to determine if two time periods overlap.

You can run this as two statements as well:

select @search_creation_date := '2021-01-26', @search_end_date := '2021-01-30' ;

select ce.*, @search_creation_date, @search_end_date
from calendars_events ce 
where . . .;

> "You can also assign the variables in a statement before the select." - What do you mean? — JarsOfJam-Scheduler, Feb 01 '21 at 21:58

My query to retrieve events between 2 search dates does not return any results, why?

Context

Example of search, Expected and Actual behaviors

The script

Question

2 Answers2