Search code examples
mysqllaraveleloquentphp-carbon

Laravel - How to get nearest time between two records

I have a table called room_monitors. In that table, I want to find closest operation that coming up. Assume that we have 2 operation in same room. One of them at 12:00pm and the other is at 15:00pm. When I send a request at 14:55 or 12:01 I want to get the 12:00pm operation. So far I have thought that maybe I could make a trick but as you see it's doesn't make sense.

 RoomMonitor::where('room',$roomId)
            ->whereBetween('operation_time',[now()->subMinutes(179)->format("H:i:s"),date('H:i:s', strtotime("+179 minutes"))])
            ->orderBy('operation_time','desc')->firstOrFail();

Can someone help me please?

Solution

  • You are trying to find the first possible operation that has lower time than now, so

    RoomMonitor::where('room',$roomId)
            ->where('operation_time', '<=', now()->format("H:i:s"))
            ->orderBy('operation_time','desc')
            ->firstOrFail();

    the where clause should query results that have lower time than now (or equal) and then you order them descendin by that time and the first should be the closest operation