How EXTRACT works in SQL? Best EXTRACT examples

How EXTRACT works in SQL? Best EXTRACT examples

When working with date and time data in SQL, extracting specific parts of a timestamp can be incredibly useful. Whether you need to retrieve just the year, month, day, or even the millisecond, SQL provides a built-in function to make this easy: EXTRACT. In this article, I’ll walk you through everything you need to know about how EXTRACT works in SQL, along with some of the best examples.

What is the EXTRACT Function in SQL?

The EXTRACT function is used to retrieve specific portions of a date or timestamp value. It allows us to extract elements like:

  • Year
  • Month
  • Day
  • Hour
  • Minute
  • Second
  • Quarter
  • Week
  • Day of the week

The syntax is straightforward:

EXTRACT(part FROM date_value)

Here, part specifies which element of the date to extract, and date_value is the date or timestamp column or value.

Basic Usage of EXTRACT

Let’s look at some basic examples of how EXTRACT works in SQL.

Extracting the Year

If you want to get the year from a date, you can use:

SELECT EXTRACT(YEAR FROM TIMESTAMP '2024-06-10 15:30:00') AS extracted_year;

This will return:

extracted_year
2024

Extracting the Month

To extract the month:

SELECT EXTRACT(MONTH FROM TIMESTAMP '2024-06-10 15:30:00') AS extracted_month;

Result:

extracted_month
6

Extracting the Day

SELECT EXTRACT(DAY FROM TIMESTAMP '2024-06-10 15:30:00') AS extracted_day;

Returns:

extracted_day
10

Advanced Uses of EXTRACT

Extracting the Quarter

To determine which quarter a date falls into, use:

SELECT EXTRACT(QUARTER FROM TIMESTAMP '2024-06-10 15:30:00') AS extracted_quarter;

Result:

extracted_quarter
2

Extracting the Week Number

If you need to know the week number of the year:

SELECT EXTRACT(WEEK FROM TIMESTAMP '2024-06-10 15:30:00') AS extracted_week;
extracted_week
24

Extracting the Day of the Week

You can determine the day of the week (Sunday = 0, Monday = 1, …):

SELECT EXTRACT(DOW FROM TIMESTAMP '2024-06-10 15:30:00') AS extracted_dow;
extracted_dow
1

Using EXTRACT with a Table

Let’s say we have a table called orders with a column order_date (type: TIMESTAMP). We can extract different date components as follows:

SELECT 
    order_id, 
    EXTRACT(YEAR FROM order_date) AS order_year, 
    EXTRACT(MONTH FROM order_date) AS order_month, 
    EXTRACT(DAY FROM order_date) AS order_day
FROM orders;

This query will return a structured breakdown of order dates by year, month, and day.

When to Use EXTRACT in SQL?

Using EXTRACT in SQL can be useful in various scenarios, including:

  1. Filtering records based on year, month, or day in a WHERE clause.
  2. Group reporting by specific date parts (e.g., analyzing sales per quarter).
  3. Generating insights based on time components.

EXTRACT vs DATEPART vs Other Date Functions

While EXTRACT is widely available in databases like PostgreSQL, MySQL, and Oracle, some systems like SQL Server use DATEPART instead.

Comparison:

Function Syntax Database
EXTRACT EXTRACT(YEAR FROM date_value) PostgreSQL, MySQL, Oracle
DATEPART DATEPART(YEAR, date_value) SQL Server

Conclusion

Understanding how EXTRACT works in SQL can significantly improve your ability to work with date and time data. Whether you’re retrieving years, months, weeks, or even hours, this function provides a straightforward way to break down timestamps into meaningful components.

By incorporating EXTRACT in your SQL queries, you can efficiently analyze and manipulate date-based data. If you’re working with databases like PostgreSQL or MySQL, this function is a great tool to have in your arsenal.

 

Other interesting article:

How DATEDIFF works in SQL? Best DATEDIFF examples