EXTRACT / DATE_PART

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)

DATE_PART (Alternative):

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;

Sample Data (orders table):

Result:

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 events;

Sample Data (events table):

Result:

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;

Result:

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 day_of_week_num;

Result:

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;

Result:

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 events;

BigQuery:

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

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)

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 orders;

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 orders;

Common Date Parts Reference

Common Use Cases

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

Performance Tips

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

Best Practices

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