Paradime | EXTRACT / DATE

SQL Keywords

EXTRACT / DATE_PART

Feb 23, 2026

min read

Category: Date/Time Functions

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

Extract specific components (year, month, day, hour, etc.) from date or timestamp values. EXTRACT is standard SQL, DATE_PART is an alternative function. Essential for date analysis, filtering, and grouping by time components.

Syntax

EXTRACT (Standard SQL):

EXTRACT(part FROM date_expression)

EXTRACT(part FROM date_expression)

EXTRACT(part FROM date_expression)

DATE_PART (Alternative):

DATE_PART(part, date_expression)

DATE_PART(part, date_expression)

DATE_PART(part, date_expression)

Available Parts:

YEAR, QUARTER, MONTH, WEEK, DAY
HOUR, MINUTE, SECOND, MILLISECOND
DAYOFWEEK, DAYOFYEAR, WEEKOFYEAR
EPOCH (seconds since 1970-01-01)

Platform-Specific Notes

Snowflake:

Both EXTRACT and DATE_PART supported
Additional: YEAROFWEEK, YEAROFWEEKISO
EPOCH for Unix timestamp
Rich set of date parts

BigQuery:

EXTRACT preferred
Additional: ISOWEEK, ISOYEAR
DATETIME parts (DATE, TIME)
WEEK starts Sunday by default

Databricks:

Both EXTRACT and DATE_PART
Standard Spark SQL functions
Also: YEAR(), MONTH(), DAY() functions
Compatible with Hive

Example 1: Extract Date Components

All Platforms:

SELECT 
    order_date,
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(QUARTER FROM order_date) as quarter,
    EXTRACT(MONTH FROM order_date) as month,
    EXTRACT(DAY FROM order_date) as day,
    EXTRACT(DAYOFWEEK FROM order_date) as day_of_week,
    EXTRACT(DAYOFYEAR FROM order_date) as day_of_year,
    EXTRACT(WEEK FROM order_date) as week_of_year
FROM orders
LIMIT 5

SELECT 
    order_date,
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(QUARTER FROM order_date) as quarter,
    EXTRACT(MONTH FROM order_date) as month,
    EXTRACT(DAY FROM order_date) as day,
    EXTRACT(DAYOFWEEK FROM order_date) as day_of_week,
    EXTRACT(DAYOFYEAR FROM order_date) as day_of_year,
    EXTRACT(WEEK FROM order_date) as week_of_year
FROM orders
LIMIT 5

SELECT 
    order_date,
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(QUARTER FROM order_date) as quarter,
    EXTRACT(MONTH FROM order_date) as month,
    EXTRACT(DAY FROM order_date) as day,
    EXTRACT(DAYOFWEEK FROM order_date) as day_of_week,
    EXTRACT(DAYOFYEAR FROM order_date) as day_of_year,
    EXTRACT(WEEK FROM order_date) as week_of_year
FROM orders
LIMIT 5

Sample Data (orders table):

order_id	order_date
101	2024-01-15
102	2024-02-20
103	2024-03-10
104	2024-06-25
105	2024-12-31

Result:

order_date	year	quarter	month	day	day_of_week	day_of_year	week_of_year
2024-01-15	2024	1	1	15	2	15	3
2024-02-20	2024	1	2	20	3	51	8
2024-03-10	2024	1	3	10	1	70	10
2024-06-25	2024	2	6	25	3	177	26
2024-12-31	2024	4	12	31	3	366	53

Example 2: Extract Time Components

All Platforms:

SELECT 
    event_timestamp,
    EXTRACT(HOUR FROM event_timestamp) as hour,
    EXTRACT(MINUTE FROM event_timestamp) as minute,
    EXTRACT(SECOND FROM event_timestamp) as second,
    -- Reconstruct time string
    CONCAT(
        LPAD(CAST(EXTRACT(HOUR FROM event_timestamp) AS VARCHAR), 2, '0'),
        ':',
        LPAD(CAST(EXTRACT(MINUTE FROM event_timestamp) AS VARCHAR), 2, '0')
    ) as time_hhmm
FROM

SELECT 
    event_timestamp,
    EXTRACT(HOUR FROM event_timestamp) as hour,
    EXTRACT(MINUTE FROM event_timestamp) as minute,
    EXTRACT(SECOND FROM event_timestamp) as second,
    -- Reconstruct time string
    CONCAT(
        LPAD(CAST(EXTRACT(HOUR FROM event_timestamp) AS VARCHAR), 2, '0'),
        ':',
        LPAD(CAST(EXTRACT(MINUTE FROM event_timestamp) AS VARCHAR), 2, '0')
    ) as time_hhmm
FROM

SELECT 
    event_timestamp,
    EXTRACT(HOUR FROM event_timestamp) as hour,
    EXTRACT(MINUTE FROM event_timestamp) as minute,
    EXTRACT(SECOND FROM event_timestamp) as second,
    -- Reconstruct time string
    CONCAT(
        LPAD(CAST(EXTRACT(HOUR FROM event_timestamp) AS VARCHAR), 2, '0'),
        ':',
        LPAD(CAST(EXTRACT(MINUTE FROM event_timestamp) AS VARCHAR), 2, '0')
    ) as time_hhmm
FROM

Sample Data (events table):

event_id	event_timestamp
1	2024-01-15 09:30:45
2	2024-01-15 14:15:30
3	2024-01-15 18:45:00

Result:

event_timestamp	hour	minute	second	time_hhmm
2024-01-15 09:30:45	9	30	45	09:30
2024-01-15 14:15:30	14	15	30	14:15
2024-01-15 18:45:00	18	45	0	18:45

Example 3: Group By Date Components

All Platforms:

SELECT 
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(MONTH FROM order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as monthly_revenue,
    AVG(total) as avg_order_value
FROM orders
GROUP BY 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month

SELECT 
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(MONTH FROM order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as monthly_revenue,
    AVG(total) as avg_order_value
FROM orders
GROUP BY 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month

SELECT 
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(MONTH FROM order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as monthly_revenue,
    AVG(total) as avg_order_value
FROM orders
GROUP BY 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month

Result:

year	month	order_count	monthly_revenue	avg_order_value
2024	1	145	42500.00	293.10
2024	2	168	51200.00	304.76
2024	3	152	47800.00	314.47

Example 4: Day of Week Analysis

All Platforms:

SELECT 
    EXTRACT(DAYOFWEEK FROM order_date) as day_of_week_num,
    CASE EXTRACT(DAYOFWEEK FROM order_date)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END as day_name,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
GROUP BY EXTRACT(DAYOFWEEK FROM order_date)
ORDER BY

SELECT 
    EXTRACT(DAYOFWEEK FROM order_date) as day_of_week_num,
    CASE EXTRACT(DAYOFWEEK FROM order_date)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END as day_name,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
GROUP BY EXTRACT(DAYOFWEEK FROM order_date)
ORDER BY

SELECT 
    EXTRACT(DAYOFWEEK FROM order_date) as day_of_week_num,
    CASE EXTRACT(DAYOFWEEK FROM order_date)
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
    END as day_name,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
GROUP BY EXTRACT(DAYOFWEEK FROM order_date)
ORDER BY

Result:

day_of_week_num	day_name	order_count	avg_order_value
1	Sunday	85	312.45
2	Monday	142	285.30
3	Tuesday	156	298.75
4	Wednesday	148	305.20
5	Thursday	153	295.80
6	Friday	189	325.50
7	Saturday	127	340.15

Example 5: Hour of Day Pattern Analysis

All Platforms:

SELECT 
    EXTRACT(HOUR FROM event_timestamp) as hour,
    CASE 
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 6 AND 11 THEN 'Morning'
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 12 AND 17 THEN 'Afternoon'
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 18 AND 22 THEN 'Evening'
        ELSE 'Night'
    END as time_of_day,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM user_events
GROUP BY EXTRACT(HOUR FROM event_timestamp)
ORDER BY hour

SELECT 
    EXTRACT(HOUR FROM event_timestamp) as hour,
    CASE 
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 6 AND 11 THEN 'Morning'
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 12 AND 17 THEN 'Afternoon'
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 18 AND 22 THEN 'Evening'
        ELSE 'Night'
    END as time_of_day,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM user_events
GROUP BY EXTRACT(HOUR FROM event_timestamp)
ORDER BY hour

SELECT 
    EXTRACT(HOUR FROM event_timestamp) as hour,
    CASE 
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 6 AND 11 THEN 'Morning'
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 12 AND 17 THEN 'Afternoon'
        WHEN EXTRACT(HOUR FROM event_timestamp) BETWEEN 18 AND 22 THEN 'Evening'
        ELSE 'Night'
    END as time_of_day,
    COUNT(*) as event_count,
    COUNT(DISTINCT user_id) as unique_users
FROM user_events
GROUP BY EXTRACT(HOUR FROM event_timestamp)
ORDER BY hour

Result:

hour	time_of_day	event_count	unique_users
0	Night	125	45
1	Night	85	32
...	...	...	...
9	Morning	1250	520
14	Afternoon	1580	680
20	Evening	980	425

Example 6: Unix Timestamp (EPOCH)

Snowflake/Databricks:

SELECT 
    event_timestamp,
    EXTRACT(EPOCH FROM event_timestamp) as unix_timestamp,
    -- Convert back from epoch
    TO_TIMESTAMP(EXTRACT(EPOCH FROM event_timestamp)) as reconstructed
FROM

SELECT 
    event_timestamp,
    EXTRACT(EPOCH FROM event_timestamp) as unix_timestamp,
    -- Convert back from epoch
    TO_TIMESTAMP(EXTRACT(EPOCH FROM event_timestamp)) as reconstructed
FROM

SELECT 
    event_timestamp,
    EXTRACT(EPOCH FROM event_timestamp) as unix_timestamp,
    -- Convert back from epoch
    TO_TIMESTAMP(EXTRACT(EPOCH FROM event_timestamp)) as reconstructed
FROM

BigQuery:

SELECT 
    event_timestamp,
    UNIX_SECONDS(event_timestamp) as unix_timestamp,
    -- Convert back
    TIMESTAMP_SECONDS(UNIX_SECONDS(event_timestamp)) as reconstructed
FROM

SELECT 
    event_timestamp,
    UNIX_SECONDS(event_timestamp) as unix_timestamp,
    -- Convert back
    TIMESTAMP_SECONDS(UNIX_SECONDS(event_timestamp)) as reconstructed
FROM

SELECT 
    event_timestamp,
    UNIX_SECONDS(event_timestamp) as unix_timestamp,
    -- Convert back
    TIMESTAMP_SECONDS(UNIX_SECONDS(event_timestamp)) as reconstructed
FROM

DATE_PART vs EXTRACT

Both are equivalent:

-- These produce the same result
EXTRACT(YEAR FROM date_column)
DATE_PART('YEAR', date_column)

EXTRACT(MONTH FROM date_column)
DATE_PART('MONTH', date_column)

-- These produce the same result
EXTRACT(YEAR FROM date_column)
DATE_PART('YEAR', date_column)

EXTRACT(MONTH FROM date_column)
DATE_PART('MONTH', date_column)

-- These produce the same result
EXTRACT(YEAR FROM date_column)
DATE_PART('YEAR', date_column)

EXTRACT(MONTH FROM date_column)
DATE_PART('MONTH', date_column)

Platform-Specific Alternatives

Databricks also supports:

SELECT 
    YEAR(order_date) as year,
    MONTH(order_date) as month,
    DAY(order_date) as day,
    DAYOFWEEK(order_date) as dow,
    HOUR(event_timestamp) as hour,
    MINUTE(event_timestamp) as minute
FROM

SELECT 
    YEAR(order_date) as year,
    MONTH(order_date) as month,
    DAY(order_date) as day,
    DAYOFWEEK(order_date) as dow,
    HOUR(event_timestamp) as hour,
    MINUTE(event_timestamp) as minute
FROM

SELECT 
    YEAR(order_date) as year,
    MONTH(order_date) as month,
    DAY(order_date) as day,
    DAYOFWEEK(order_date) as dow,
    HOUR(event_timestamp) as hour,
    MINUTE(event_timestamp) as minute
FROM

Snowflake also supports:

SELECT 
    YEAR(order_date) as year,
    MONTH(order_date) as month,
    DAY(order_date) as day,
    DAYOFWEEK(order_date) as dow,
    HOUR(event_timestamp) as hour,
    MINUTE(event_timestamp) as minute
FROM

SELECT 
    YEAR(order_date) as year,
    MONTH(order_date) as month,
    DAY(order_date) as day,
    DAYOFWEEK(order_date) as dow,
    HOUR(event_timestamp) as hour,
    MINUTE(event_timestamp) as minute
FROM

SELECT 
    YEAR(order_date) as year,
    MONTH(order_date) as month,
    DAY(order_date) as day,
    DAYOFWEEK(order_date) as dow,
    HOUR(event_timestamp) as hour,
    MINUTE(event_timestamp) as minute
FROM

Common Date Parts Reference

Part	Description	Example Value
YEAR	4-digit year	2024
QUARTER	Quarter (1-4)	1
MONTH	Month (1-12)	3
WEEK	Week of year (1-53)	12
DAY	Day of month (1-31)	15
DAYOFWEEK	Day of week (1-7)	2 (Monday)
DAYOFYEAR	Day of year (1-366)	75
HOUR	Hour (0-23)	14
MINUTE	Minute (0-59)	30
SECOND	Second (0-59)	45
EPOCH	Seconds since 1970-01-01	1705329000

Common Use Cases

Temporal grouping: Aggregate by month, quarter, year
Pattern analysis: Day of week, hour of day trends
Filtering: Find records from specific months or hours
Business logic: Fiscal year calculations
Reporting: Year-over-year comparisons
Scheduling: Route based on time of day

Performance Tips

Avoid EXTRACT in WHERE: Can prevent index use
Create computed columns: For frequently extracted parts
Use DATE_TRUNC for ranges: More efficient than EXTRACT
Index extracted values: If used frequently in WHERE
Materialize common extractions: In views or tables

Best Practices

Use consistent timezone: Especially with timestamps
Document week start day: Different platforms may vary
Handle NULL dates: Use COALESCE if needed
Use appropriate granularity: Don't extract more than needed
Consider fiscal vs calendar: For business reporting
Test edge cases: Month boundaries, leap years, DST
Use DATE_TRUNC for grouping: Often more efficient

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources