I am using an eloquent query to retrieve data from a table. The table columns look like this:
id started_at finished_at
1 2016-06-01 2016-06-30
2 2016-05-03 2016-05-28
What I want to do is, given a $date (ex: 2016-06-18 ) and get the data of the row, that the $date between started at and finished_at columns.
I have found whereBetween clauses in Laravel documentation, but I do not have an idea to use it correctly.
If you can use Carbon then this code will work fine for you.
$dateS = new Carbon('first day of January 2016');
$dateE = new Carbon('first day of November 2016');
$result = ModelName::whereBetween('created_at', [$dateS->format('Y-m-d')." 00:00:00", $dateE->format('Y-m-d')." 23:59:59"])->get();
Or you can check the issue by debugging query using DB::enableQueryLog(); and DB::getQueryLog(); functions like
DB::enableQueryLog();
$dateS = new Carbon('first day of January 2016');
$dateE = new Carbon('first day of November 2016');
$result = ModelName::whereBetween('created_at', [$dateS->format('Y-m-d')." 00:00:00", $dateE->format('Y-m-d')." 23:59:59"])->get();
var_dump($result, DB::getQueryLog());
Try to do something like this:
$date1 = Carbon::today()->toDateString();
$date2 = Carbon::today()->toDateString();
$myModel = MyModel::find(1);
$myModel->whereBetween('created_at', [$date1, $date2]);
$myModel->get();
Of course, you will need to change the dates.
You can use whereBetween something like this:-
ModelName::whereBetween('date',['2022-08-01','2022-10-30'])->get();
Import carbon class in your controller
use Carbon\Carbon;
For date time column:
ModelName::whereBetween('column_name',[Carbon::parse('2016-06-01')->format('Y-m-d 00:00:00'),Carbon::parse('2016-06-30')->format('Y-m-d 23:59:59')])->get();
For date column
ModelName::whereBetween('column_name',[Carbon::parse('2016-06-01')->format('Y-m-d'),Carbon::parse('2016-06-30')->format('Y-m-d')])->get();
Note: date may come from user input so carbon will format the date.
$startDate = '2024-5-17';
$endDate = '2024-5-17';
if the column is timestamp or datetime then it will generate a query like
User::whereBetween('created_at',[$startDate, $endDate])->get();
// SELECT * FROM users where created_at between 2024-5-17 00:00:00 and 2024-5-17 00:00:00
if you use the date(created_at) MySQL function then it will only focus on the date, not the time
use Illuminate\Support\Facades\DB;
User::whereBetween(DB::raw('DATE(created_at)'),[$startDate, $endDate])->get();
// SELECT * FROM users where created_at between 2024-5-17 and 2024-5-17
or you can do
User::whereBetween('created_at',[$startDate, $endDate.' 23:59:59'])->get();
// SELECT * FROM users where created_at between 2024-5-17 00:00:00 and 2024-5-17 23:59:59