CODEWITHSUNDEEP
mysqlruby-on-railstimerange-query
Tony Petruzzi
Tony Petruzzi

Reputation: 109

Rails querying across midnight with time only column

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.

Upvotes: 0

Views: 169

Answers (1)

Tony Petruzzi
Tony Petruzzi

Reputation: 109

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

Upvotes: 0

Related Questions