-1

I use Laravel 8 and MySQL 5.6

I have a table and there's a 'date_time' column. Data in this column look like "2022-10-03 10:43" or "2022-10-09 19:00" or any another date and time.

So I need to get rows where the time is between 18:00 (the current day) and 06:00 (the next day). If I use this:

WHERE (TIME(`date_time`) BETWEEN "18:00:00" AND "06:00:00")

It doesn't work because MySQL checks times in one day, so I use another condition:

WHERE 
  (TIME(`date_time`) BETWEEN "18:00:00" and "24:00:00") 
  or (TIME(`date_time`) BETWEEN "00:00:00" and "06:00:00")

There's any way to make conditions a little shorter or maybe Laravel has any solutions?

musiermoore
  • 1
  • 4
  • Does this answer your question? [How to query between two dates using Laravel and Eloquent?](https://stackoverflow.com/questions/33361628/how-to-query-between-two-dates-using-laravel-and-eloquent) – steven7mwesigwa Oct 07 '22 at 05:18
  • Thanks, but I think it doesn't because there're dateTime columns in that question, but I need to convert dateTime to Time and then check if the time is between 6 pm and 6 am. – musiermoore Oct 07 '22 at 05:34
  • [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) – steven7mwesigwa Oct 07 '22 at 06:02

1 Answers1

-1

whereBetween / orWhereBetween

The whereBetween method verifies that a column's value is between two values:

$bookings = DB::table('bookings')
    ->whereBetween('date_time', [
        today()->toDateString() . " 18:00",
        today()->addDays(1)->toDateString() . " 06:00",
    ])
    ->get();
steven7mwesigwa
  • 5,701
  • 3
  • 20
  • 34
  • 1
    ... ``today()`` :p – OMi Shah Oct 07 '22 at 06:01
  • @OMiShah Thanks for the tip. Modified answer! – steven7mwesigwa Oct 07 '22 at 06:05
  • Oh, sorry, I didn't specify that the date can be any date. It's not just today, it could be today or 20 days from now (any date). But I think I can use `whereRaw` and something like this: `whereRaw('date_time BETWEEN CONCAT(DATE(date_time), " 18:00:00") and CONCAT(DATE(date_time) + INTERVAL 1 DAY, " 06:00:00")')` – musiermoore Oct 07 '22 at 07:04
  • @musiermoore That query doesn't make sense at all to me. In addition, the generated SQL query would be invalid. – steven7mwesigwa Oct 07 '22 at 07:25
  • 1
    Yes, you're right, it doesn't work and looks bad, but I just searched another way. I found one way and it works, but I don't know it's ok or not. So a little later, I'll write an answer. Thank you for your help! – musiermoore Oct 07 '22 at 08:31