Sling Academy
Home/PHP/Eloquent: Find Records Between Two Dates

Eloquent: Find Records Between Two Dates

Last updated: January 16, 2024

Introduction

When working with applications that have time-sensitive data, it is often necessary to retrieve records that fall between two specific dates. In Laravel’s Eloquent, this is a common task for developers. Eloquent provides an elegant and fluent interface to query the database with an expressive syntax. In this tutorial, we will discuss multiple ways to achieve date range queries using Eloquent.

Prerequisites

  • Basic knowledge of Laravel and MVC architecture
  • Working Laravel environment
  • A database with at least one table containing date or datetime columns

Using whereBetween Method

The simplest way to retrieve records between two dates in Eloquent is by using the whereBetween method. Here’s a basic example:


$records = ModelName::whereBetween('date_column', ['2023-01-01', '2023-01-31'])->get();

The above code will retrieve records from the ModelName where the date_column is between ‘2023-01-01’ and ‘2023-01-31’. Below is a detailed guide with multiple examples to help you master date range queries in Eloquent.

Basic Date Range Query

Let’s start with a basic example:


use App\Models\Order;

// Fetch orders between two dates
$startDate = '2021-07-01';
$endDate = '2021-07-15';
$orders = Order::whereBetween('created_at', [$startDate, $endDate])->get();

// Output
foreach ($orders as $order) {
    echo $order->id . ' - ' . $order->created_at . "\n";
}

This will output the IDs and ‘created_at’ dates of the orders that were placed between July 1, 2021, and July 15, 2021.

Advanced Usage with Time

Sometimes, you may want to filter records by datetime, not just by date. In this case, you will need to include the time in your filtering:


$orders = Order::whereBetween('created_at', [
    '2021-07-01 00:00:00',
    '2021-07-15 23:59:59'
])->get();

This example includes the full datetime range for the provided dates. It will select all orders that were placed in the first 15 days of July 2021, up until the last second of the last day.

Using Carbon for Date Manipulation

Working with raw date strings is fine, but sometimes it is more convenient to work with a date manipulation library like Carbon, which is built into Laravel. Below is an example of how you can use Carbon:


use Carbon\Carbon;

$startDate = Carbon::createFromFormat('Y-m-d H:i:s', '2021-07-01 00:00:00');
$endDate = Carbon::createFromFormat('Y-m-d H:i:s', '2021-07-15 23:59:59');

$orders = Order::whereBetween('created_at', [$startDate, $endDate])->get();

Carbon allows you to handle dates in a much more approachable and flexible way, including the manipulation of dates, differences between dates, and much more.

Scope for Date Range Query

To make our code cleaner, we could define a local scope within our Eloquent model:


use Illuminate\Database\Eloquent\Builder;

// Inside your Eloquent Model

public function scopeCreatedBetween(Builder $query, $startDate, $endDate) {
    return $query->whereBetween('created_at', [$startDate, $endDate]);
}

// Usage of the scope
$orders = Order::createdBetween($startDate, $endDate)->get();

Scopes can be incredibly useful to keep your controller actions more readable and maintain a separation of concerns within your model layer.

Dynamically Generating Date Ranges

To dynamically generate a range of dates for the previous week, month, year, etc., you can do it like this:


$startDate = Carbon::now()->startOfWeek();
$endDate = Carbon::now()->endOfWeek();

$orders = Order::createdBetween($startDate, $endDate)->get();

This will give you orders made from the start to the end of the current week.

Conclusion

Whether you’re running basic or complex queries, Eloquent’s fluency in handling a date range is an invaluable asset. We’ve explored the primary ways to capture data within a specified timeline, reinforcing the significance of Laravel’s eloquent ORM for efficient database interaction.

Next Article: Eloquent: Find records in last day/week/month/year

Previous Article: Eloquent: Find records where column is null/not null

Series: Laravel & Eloquent Tutorials

PHP

You May Also Like

  • Pandas DataFrame.value_counts() method: Explained with examples
  • Constructor Property Promotion in PHP: Tutorial & Examples
  • Understanding mixed types in PHP (5 examples)
  • Union Types in PHP: A practical guide (5 examples)
  • PHP: How to implement type checking in a function (PHP 8+)
  • Symfony + Doctrine: Implementing cursor-based pagination
  • Laravel + Eloquent: How to Group Data by Multiple Columns
  • PHP: How to convert CSV data to HTML tables
  • Using ‘never’ return type in PHP (PHP 8.1+)
  • Nullable (Optional) Types in PHP: A practical guide (5 examples)
  • Explore Attributes (Annotations) in Modern PHP (5 examples)
  • An introduction to WeakMap in PHP (6 examples)
  • Type Declarations for Class Properties in PHP (5 examples)
  • Static Return Type in PHP: Explained with examples
  • PHP: Using DocBlock comments to annotate variables
  • PHP: How to ping a server/website and get the response time
  • PHP: 3 Ways to Get City/Country from IP Address
  • PHP: How to find the mode(s) of an array (4 examples)
  • PHP: Calculate standard deviation & variance of an array