Rails querying across midnight with time only column

Question

I'm using Rails 6.0.2 and MySQl version 5.7.

On the camera_events table I have a start_time and an end_time column that are both time datatypes as I do not need the date, i just need to know when the event starts and when it ends.

I am trying to query the table to get all the events that fall BETWEEN the start_time and end_time. the problem is that any event that has it's time span across midnight or has it end_time set to midnight as it is recorded in the database as 00:00.

My issue is trying to find any events that fall between a certain time like 22:00 and 02:00.

Take a look at [my answer to this other similar question](https://stackoverflow.com/a/30536227/634824). Basically, you should use half-open intervals and write your query like the last pseudocode example shown there. — Matt Johnson-Pint, Aug 26 '20 at 21:06

score 0 · Answer 1 · answered Aug 26 '20 at 13:40

At 2AM this morning I had an idea and I actually solved my problem. The way I had to do this was to break the query up into 2 where statements with an or. Basically what I'm doing is checking to see if the end_time is greater than the start_time (for entire like 01:00 to 10:00) and for where the end_time is less than the start_time (which means that the end_time spanned midnight like 22:00 to 02:00).

  def time_span_search(current_time)
    CameraEvent
        .where('end_time > start_time and start_time <= ? and end_time >= ?', current_time, current_time)
        .or(
            CameraEvent
                .where('end_time <= start_time and start_time <= ? and start_time <= ? and end_time >= ? and end_time <= ?', current_time, '23:59', '00:00', current_time) )
        .pluck(:id)
  end

Rails querying across midnight with time only column

1 Answers1