SQL EXTRACT

What is the SQL EXTRACT function?

Returns a specific component (year, month, day, hour, etc.) from a date, time, timestamp, or interval value.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL EXTRACT: PostgreSQL, MySQL 5.7+, MariaDB, SQL Server (using DATEPART), Oracle, Snowflake, BigQuery, Redshift, SQLite (via strftime equivalents)

SQL EXTRACT Full Explanation

EXTRACT isolates and returns an individual date- or time-part as a numeric value from a date, time, timestamp, or interval expression. The function is ANSI-SQL compliant and widely implemented across major databases. Unlike DATE_TRUNC, which zeroes out lower-order fields, EXTRACT only pulls the requested part, leaving the source value unchanged. The returned data type is integer or numeric depending on the part. For intervals, EXTRACT can return larger units such as epoch (total seconds). When applied to TIMESTAMP WITH TIME ZONE, the value is first converted to the session time zone before extraction. Null input results in null output. Precision beyond supported range triggers an error in some systems.

SQL EXTRACT Syntax

EXTRACT(<field> FROM <source>)

SQL EXTRACT Parameters

field (text) - Date or time part to extract (e.g., YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, EPOCH, DOW, DOY).
source (date/time expression) - A column, literal, or expression of type DATE, TIME, TIMESTAMP, or INTERVAL.

Example Queries Using SQL EXTRACT

-- Get birth year of users
SELECT EXTRACT(YEAR FROM birth_date) AS birth_year
FROM users;

-- Determine hour of each order
SELECT order_id,
       EXTRACT(HOUR FROM created_at) AS order_hour
FROM orders;

-- Seconds since 1970-01-01
SELECT EXTRACT(EPOCH FROM NOW()) AS current_epoch;

-- Interval example: total minutes in support call duration
SELECT EXTRACT(EPOCH FROM call_duration) / 60 AS minutes_spent
FROM support_calls;

Expected Output Using SQL EXTRACT

Each query returns a numeric column containing the extracted component: years for birth_year, 0-23 for order_hour, seconds for current_epoch, or minutes_spent as a decimal

Use Cases with SQL EXTRACT

Bucketing or grouping by year, month, or hour without altering the original timestamp.
Calculating age or tenure by extracting year and comparing.
Converting timestamps to Unix epoch for cross-system interchange.
Deriving weekday (DOW) or day of year (DOY) for time-series analysis.
Pulling interval components for duration analytics.

Common Mistakes with SQL EXTRACT

Misspelling the field name (e.g., MOTH instead of MONTH).
Using quotation marks around field identifiers, causing them to be treated as strings.
Forgetting that EXTRACT returns numeric, not date, leading to type mismatches.
Assuming zero-based month or day numbering; months start at 1, days of week vary by dialect.
Applying EXTRACT to a string without casting to a date/time type.

First Introduced In

SQL-92

Frequently Asked Questions

How is EXTRACT different from DATE_TRUNC?

EXTRACT returns only the specified component as a number, while DATE_TRUNC resets all lower-order fields to zero and returns a full timestamp.

Can I extract milliseconds?

Many databases allow `SECOND` with a fractional part. For finer resolution, multiply the fractional seconds: `EXTRACT(EPOCH FROM ts) * 1000`.

What happens with NULL input?

EXTRACT returns NULL when the source expression is NULL.

Does the session time zone affect results?

Yes. For TIMESTAMP WITH TIME ZONE values, most systems convert to the current session time zone before extracting, which can change the result of fields like HOUR or DOW.