How to Use the EXTRACT() Function in Laravel Queries & SQL Queries - Complete Guide

Introduction

In web applications, date and time manipulation is a frequent requirement. Whether you're building a blog, an e-commerce platform, or any data-driven system, efficiently managing and querying dates is crucial. Laravel, as one of the most popular PHP frameworks, provides seamless integration with SQL databases. One such essential function in SQL for working with dates is the EXTRACT() function.

In this article, we will explore how to use the EXTRACT() function in both Laravel Eloquent and raw SQL queries. We will also cover its practical use cases, complete with examples and best practices for your Laravel application.

What is the EXTRACT() Function?

The EXTRACT() function is a SQL date/time function that allows you to extract specific parts of a date (such as the year, month, day, or even hour) from a datetime or timestamp column in your database.

For instance, if you have a column with a timestamp, the EXTRACT() function can help you extract the year, month, or even the day of the week from that timestamp.

Syntax of the EXTRACT() Function in SQL

EXTRACT(part FROM date)

• part: The part of the date you want to extract. This could be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
• date: The datetime or timestamp column from which you want to extract the part.

Example of Using EXTRACT() in SQL

SELECT EXTRACT(YEAR FROM created_at) AS year, 
       EXTRACT(MONTH FROM created_at) AS month
FROM users;

This SQL query extracts the year and month from the created_at timestamp column of the users table.

How to Use EXTRACT() in Laravel Query Builder

Laravel allows you to write raw SQL queries using the DB::raw() function within the Query Builder. This is how you can use the EXTRACT() function in Laravel.

Example 1: Extracting Year and Month from a Timestamp

use Illuminate\Support\Facades\DB;

$users = DB::table('users')
    ->select(DB::raw('EXTRACT(YEAR FROM created_at) as year, EXTRACT(MONTH FROM created_at) as month'))
    ->get();

foreach ($users as $user) {
    echo "Year: " . $user->year . " - Month: " . $user->month;
}

Example 2: Extracting Day of the Week

$users = DB::table('users')
    ->select(DB::raw('EXTRACT(DOW FROM created_at) as day_of_week'))
    ->get();

foreach ($users as $user) {
    echo "Day of the Week: " . $user->day_of_week;
}

The above example retrieves the day of the week (0 = Sunday, 1 = Monday, and so on) from the created_at column.

Using EXTRACT() in Laravel Eloquent Queries

If you're working with Eloquent ORM in Laravel, you can also use raw SQL to apply the EXTRACT() function.

Example 3: Extracting Year from a Timestamp in Eloquent

use App\Models\User;
use Illuminate\Support\Facades\DB;

$users = User::select(DB::raw('EXTRACT(YEAR FROM created_at) as year'))
    ->get();

foreach ($users as $user) {
    echo "Year: " . $user->year;
}

Eloquent makes it very easy to integrate raw SQL and still take advantage of Laravel's powerful ORM features.

Real-World Use Cases of EXTRACT() in Laravel

1. Filtering Reports by Date Parts: You can use EXTRACT() to create reports filtered by specific parts of the date. For example, if you want to filter all user registrations by the year or month, you can leverage EXTRACT().

   $users = User::select(DB::raw('EXTRACT(MONTH FROM created_at) as month'))
                ->groupBy('month')
                ->get();
   

    

2. Display Day of the Week: For applications like a scheduling or booking system, displaying the day of the week is important. Using EXTRACT(DOW) allows you to dynamically fetch and display the weekday from a datetime column.

Conclusion

The EXTRACT() function is a powerful and flexible tool that allows developers to retrieve specific parts of a datetime column with ease. In Laravel, it can be seamlessly integrated into both Query Builder and Eloquent. By using it, you can create efficient queries for date filtering, reporting, or other time-based calculations.

Whether you're a Laravel beginner or a seasoned developer, leveraging SQL functions like EXTRACT() will undoubtedly enhance your application's performance and flexibility.