Three methods are explained to retrieve data between the start and end dates of a model in a Laravel 10 project.
The French version of this publication : Laravel : Récupérer les données d'un modèle entre deux dates
Introduction
Retrieving table entries in the interval of two dates (initial date and final date) can allow you to limit the results to display them in graphs, tables, ... over a specific time period.
In this article, we will see how to retrieve data from a model between two specific dates in a Laravel project. To do so, we will use the where()
, whereDate()
and whereBetween()
methods.
At the time of writing this article, I am using Laravel version 10.5.1.
Method 1: where
The where($column, $operators, $date)
method retrieves data from a table using the $operators
comparison operators on the $date
by checking if a value in the $column
column meets the criteria.
Example: Let's retrieve the users (model User
) registered (created_at) between January 1st 2022 (included) and January 01st 2023 (excluded):
$date_1 = new Carbon("2022-01-01"); // Initial date
$date_2 = new Carbon("2023-01-01"); // Final date
$users= User::where("created_at", ">=", $date_1)->where('created_at', "<", $date_2)->orderBy('created_at')->get();
The following SQL query is executed in this example:
select * from `users` where `created_at` >= $date_1 and `created_at` < $date_2 order by `created_at` asc
Method 2: whereDate
Similar to the where()
method, the whereDate($column, $operators, $date)
method retrieves data from a table by comparing the datetime or timestamp field $date
with the values of the $column
column using the $operators
comparison operators.
Let's go back to the previous example, this time using the whereDate()
method:
$users= User::whereDate("created_at", ">=", $date_1)->whereDate('created_at', "<", $date_2)->orderBy('created_at')->get();
The following SQL query is executed in this example:
select * from `users` where date(`created_at`) >= $date_1 and date(`created_at`) < $date_2 order by `created_at` asc
Method 3: whereBetween
The whereBetween($column, $date_interval)
method filters the data in a table by the $column
column and between the dates specified in the $date_interval
array.
Using the whereBetween()
method, our example becomes :
$users = User::whereBetween("created_at", [ $date_1, $date_2 ])->orderBy('created_at')->get();
The following SQL query is executed in this example:
select * from `users` where `created_at` between $date_1 and $date_2 order by `created_at` asc
We have just seen three techniques to retrieve data between two dates using the where()
, whereDate()
and whereBetween()
methods in a Laravel project. Through the examples, we have seen the different SQL queries executed. You are free to use the method that suits you 😜
Sincerely.