How to Retrieve Data Between Two Dates With Laravel Eloquent

When dealing with date-based columns, such as date or datetime, it often becomes necessary to retrieve records between two dates. In this tutorial, we will explore three different solutions to run this type of query and compare the solutions based on their raw SQL.

By the end of this tutorial, you will have a clear understanding of the different approaches and recommendations for querying records between two dates in Laravel. Let’s get started!

Solution 1: Using whereBetween

The whereBetween method in Laravel Eloquent provides the most readable way to retrieve records between two dates. Here’s an example of how to use it with a Post model:

$startDate = '2000-01-01';
$endDate = '2023-06-22';

$posts = Post::whereBetween('published_at', [$startDate, $endDate])->get();

Raw SQL

This solution translates to a single SQL condition using the BETWEEN operator. Let’s examine the raw SQL generated by this solution:

SELECT * FROM posts
WHERE published_at BETWEEN '2000-01-01' AND '2023-06-22'

To view the raw SQL generated by Laravel, I recommend using the Laravel Debugbar. Check out this post for instructions on installing and using it, along with other methods to retrieve raw SQL: How to Get Raw SQL Query From Laravel Query Builder or Model.

Solution 2: Using Two where Conditions

Another way to retrieve data between two dates is to use two separate where conditions to specify the date range. Here’s an example:

$startDate = '2000-01-01';
$endDate = '2023-06-22';

$posts = Post::where('published_at', '>=', $startDate)
             ->where('published_at', '<=', $endDate)
             ->get();

Raw SQL

This solution translates to 2 SQL conditions using comparison operators >= and <=. Let’s examine the raw SQL generated by this solution:

SELECT * FROM posts
WHERE published_at >= '2000-01-01'
AND published_at <= '2023-06-22'

As you can see from the raw SQL, Solution 2 is equivalent to Solution 1 in terms of both the results and performance.

Solution 3: Using Two whereDate Conditions

If you only need to compare the date portion of the attribute, you can use the whereDate method. It allows us to match records based on the date part only, ignoring the time. Here’s an example:

$startDate = '2000-01-01';
$endDate = '2023-06-22';

$posts = Post::whereDate('published_at', '>=', $startDate)
             ->whereDate('published_at', '<=', $endDate)
             ->get();

Raw SQL

This solution translates to two SQL conditions using comparison operators >= and <= while using SQL DATE function to reduce the comparison to the date stripped of any time value (i.e. hours, minutes, seconds). Let’s examine the raw SQL generated by this solution:

SELECT * FROM posts
WHERE DATE(published_at) >= '2000-01-01'
AND DATE(published_at) <= '2023-06-22'

As the raw SQL shows, using Solution 3 means the SQL DATE function is applied, which extracts only the date portion of the column value. If the published_at column has a datetime or timestamp data type, the time component (hours, minutes, seconds) will be removed, and only the date will be compared.

This approach is especially useful when you want to focus solely on the date aspect and exclude any variations in time.

Conclusion

In this tutorial, we explored three effective techniques to query records between two dates using Laravel Eloquent. We covered the whereBetween method, using the where clause twice, and leveraging the whereDate method. Each technique offers its advantages, so choose the one that best suits your requirements. Mastering these techniques will enable you to effectively handle date-based filtering in your Laravel applications.

Start applying these techniques in your projects today. Happy coding!