There are so many different date functions in SQL—you have DATEDIFF, DATEADD, DATE_PART, and DATE_TRUNC to name a few. They all have their different use cases and understanding how and when they should be used is a SQL fundamental to get down. Are any of those as easy to use as the EXTRACT function? Well, that debate is for another time…
In this post, we’re going to give a deep dive into the EXTRACT function, how it works, and why we use it.
The EXTRACT function allows you to extract a specified date part from a date/time. For example, if you were to extract the month from the date February 14, 2022, it would return 2 since February is the second month in the year.
What is a SQL function? At a high level, a function takes an input (or multiple inputs) and returns a manipulation of those inputs. Some common SQL functions are COALESCE, LOWER and DATEDIFF. For example, the COALESCE function takes a group of values and returns the first non-null value from that group.
How to use the EXTRACT function
One of our favorite things about the EXTRACT function is how readable it is. Sometimes you may encounter SQL functions and not immediately understand what the arguments are and what the expected output should be. (We’re looking at you, SPLIT_PART.) The EXTRACT function isn’t like that.
To use the EXTRACT function, you’ll simply specify the date part you want extracted out and the field you want to extract from. You can extract many different date parts, but you’ll most commonly see year, month, week of year, or quarter extracted from a date.
extract(<date_part> from <date/time field>)
Depending on the data warehouse you use, the value returned from an EXTRACT function is often a numeric value or the same date type as the input <date/time field>. Read the documentation for your data warehouse to better understand EXTRACT outputs.
Note: You may additionally see a comma used in place of the ‘from’ in the EXTRACT function, like
extract(<date_part>, <date/time field>). We feel that using that ‘from’ in the function makes it a little more readable.
The DATE_PART function
You may also see the DATE_PART function used in place of the EXTRACT function. Both DATE_PART and EXTRACT perform the same functionality, it’s just a matter of preference on which one you want to use.
Postgres & DATE_PART: This is overly pedantic and you’ll likely never encounter an issue with DATE_PART and EXTRACT evaluating to differences in values that truly matter, but it’s worth noting. Postgres’ DATE_PART and EXTRACT functions would previously evaluate to the same output. However, with Postgres 14, the EXTRACT function now returns a numeric type instead of an 8-byte float.
Data warehouse support for the EXTRACT function
Google BigQuery, Amazon Redshift, Snowflake, Postgres, and Databricks all support the EXTRACT function. In addition, the syntax to use EXTRACT is the same across all of them.
EXTRACT function example
Let’s take this to an actual example! We’re going to use the jaffle shop, a simple dataset and dbt project, to help us. The jaffle shop’s orders table has some fields around an order’s status, order date, and order amount.
You can extract different time-based values (weeks, months, years, etc.) from the order_date in the orders model using the following code:
select
order_id,
order_date,
extract(week from order_date) as order_week,
extract(month from order_date) as order_month,
extract(year from order_date) as order_year
from {{ ref('orders') }}
After running this query, your results would look a little something like this:
| order_id | order_date | order_week | order_month | order_year |
|---|---|---|---|---|
| 1 | 2018-01-01 | 1 | 1 | 2018 |
| 9 | 2018-01-12 | 2 | 1 | 2018 |
| 72 | 2018-03-23 | 12 | 3 | 2018 |
As you can see, this query extracted the week of year, month of year, and year from the order_date.
Why we love it
We’re going to be honest: EXTRACT isn’t the most widely used SQL function in our dbt project. However, EXTRACT has its time and place:
- Fiscal calendars: If your business uses fiscal years, or calendars that differ from the normal 12-month cycle, EXTRACT functions can help create alignment between fiscal calendars and normal calendars
- Ad hoc analysis: EXTRACT functions are useful in ad hoc analyses and queries when you need to look at values grouped by date periods or for period comparisons
Extract is a consistent, helpful, and straightforward function–what more could we ask for from a friend function?
Comments