Laravel Query Builder: Selecting Rows Between Two Dates

Introduction

Laravel is a popular PHP framework known for its expressive and elegant syntax. Developers often need to fetch data within a specific date range from databases, which can be a common requirement for applications involving data filtering based on time frames such as reports, logs, or analytics. In Laravel, this task is made straightforward with the help of the Query Builder – an interface which provides a simple, chainable method to construct database queries. In this tutorial, we will guide you through the process of using Laravel’s Query Builder to select rows between two dates.

Setting the Foundation

Before diving into the actual code, ensure that you’ve set up a Laravel project and configured your database settings in the .env file. For the following examples, we will assume that we have a table called orders with a created_at date-type column.

Basic Usage of whereBetween() Method

To start, let’s look at a simple query selecting all orders between two dates:

use Illuminate\Support\Facades\DB;

$orders = DB::table('orders')
    ->whereBetween('created_at', ['2023-01-01', '2023-01-31'])
    ->get();

This will retrieve orders created within January 2023. The whereBetween method is used here, taking the column name as the first argument and an array of the start and end dates as the second.

Using Carbon for Date Manipulation

Working with dates in PHP is made much easier with the Carbon library, which is integrated into Laravel by default.

use Carbon\Carbon;

$start = Carbon::parse('first day of last month');
$end = Carbon::parse('last day of last month');

$orders = DB::table('orders')
    ->whereBetween('created_at', [$start, $end])
    ->get();

The above code selects all orders from the previous month by dynamically setting the date range using Carbon’s human-friendly methods.

Advanced Eloquent Queries with Date Ranges

Laravel Eloquent ORM provides an elegant and more readable alternative for querying databases, which can be combined with advanced query techniques:

use App\Models\Order;
use Carbon\Carbon;

$start = Carbon::now()->subDays(30);
$end = Carbon::now();

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

The above example uses Eloquent to fetch orders from the past 30 days. Eloquent’s methods enable you to build more complex queries with relative ease.

Incorporating Time with Date Range Selection

Sometimes, you need to consider the time component of your date columns when filtering by date range. Here’s how you could modify the query to consider time:

$orders = DB::table('orders')
    ->whereBetween('created_at', [
        '2023-01-01 08:00:00', 
        '2023-01-31 17:00:00'
    ])
    ->get();

This modification ensures that only orders created between 8 AM on January 1st and 5 PM on January 31st, 2023, are selected. Custom timestamps are provided in the date range array.

Conclusion

Using Laravel Query Builder or Eloquent ORM to select rows between two specific dates is an efficient way to extract relevant data based on time frames. Whether you’re working with simple or complex databases, Laravel provides the tools you need to write clean and maintainable code for such operations.