Dates: Intermediate

Pull year, month, or day from a date

EXTRACT pulls a single component from a date or timestamp and returns it as a number. It allows you to isolate the year, month, day, hour, minute, second, or other parts for analysis, grouping, or filtering. This function is fundamental for temporal aggregations.

The syntax is EXTRACT(part FROM date_or_timestamp). The part specifies which component to extract: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAY_OF_WEEK, DAY_OF_YEAR, WEEK, and QUARTER are commonly supported. The result is always an integer.

Each EXTRACT call returns a single integer. YEAR gives the full year (2024, not 24). MONTH returns 1-12. DAY returns 1-31 depending on the month. These integers enable numeric comparisons and grouping.

EXTRACT also works with timestamps to pull time-of-day components:

HOUR returns 0-23 in 24-hour format. MINUTE and SECOND return 0-59. These extractions enable time-of-day analysis, peak hour detection, and shift-based reporting.

EXTRACT supports higher-level calendar components:

EXTRACT powers temporal aggregations by providing grouping keys:

This query aggregates orders by year and month. The extracted components become the GROUP BY keys, creating monthly summaries. This pattern is foundational for time-series reporting.

EXTRACT enables selective filtering by temporal component:

This query finds December weekend orders. The MONTH filter restricts to December regardless of year. The DAY_OF_WEEK filter keeps only weekend days. Combining extractions creates precise temporal filters.

EXTRACT is most valuable when you need to analyze patterns or group data by specific time components.

These are the most common scenarios where EXTRACT proves invaluable.

Time-based grouping

Aggregate by year, month, quarter, or week for reporting

Component filtering

Select specific months, weekdays, or hours of operation

Pattern analysis

Detect hourly, daily, or seasonal trends in data

SELECT
  ___(___ ___ signup_date) AS year
FROM users

EXTRACT always returns an integer. You can use the result directly in arithmetic, comparison operators, and GROUP BY without any additional casting.

For large tables, using EXTRACT in WHERE clauses prevents the optimizer from using date-range indexes. Prefer range comparisons on the original column when filtering by year or month.

When building cohort analysis, EXTRACT(YEAR FROM signup_date) combined with EXTRACT(MONTH FROM signup_date) gives a precise month-of-signup cohort key without any string formatting.

Reference any component of a timestamp

EXTRACT supports a variety of date part constants beyond YEAR, MONTH, and DAY. Knowing all available parts lets you slice temporal data at any granularity, from quarters down to seconds.

QUARTER returns 1 through 4 and is essential for fiscal reporting. DOW gives the day of week (1 = Monday through 7 = Sunday). DOY gives the day of year (1 to 366), useful for seasonal analysis. For timestamps, HOUR, MINUTE, and SECOND extract time components.

Combining EXTRACT with GROUP BY lets you aggregate by any time period: weekly sales, quarterly revenue, hourly traffic patterns. This is one of the most common patterns in analytics.

CURRENT_TIMESTAMP provides the complete current moment with date and time. You can extract specific components like hour, day, or month to build time-aware logic directly in your queries.

SELECT
  ___(___ ___ order_date) AS month
FROM orders

EXTRACT(MONTH) returns the same integer 1-12 regardless of the year. If your analysis spans multiple years, always pair it with EXTRACT(YEAR) in GROUP BY to avoid mixing months across different years.

NOW() and CURRENT_TIMESTAMP are functionally identical in most databases. NOW() is shorter for interactive queries, while CURRENT_TIMESTAMP is the ANSI SQL standard and preferred in production code for clarity.

Calculate precise intervals between dates

DATE_DIFF calculates the difference between two timestamps or dates. It returns the result as an integer representing the number of complete units between them.

The syntax is DATE_DIFF(unit, timestamp1, timestamp2). The function returns timestamp2 - timestamp1 in the specified unit. Common units include DAY, HOUR, MINUTE, and SECOND.

DATE_DIFF counts complete units. Session S001 lasted 45 minutes and 30 seconds, which rounds down to 45 complete minutes or 2730 complete seconds. Partial units are not counted.

DATE_DIFF powers time-based metrics across industries.

SELECT
  job_id,
  ___(
    ___,
    ___,
    ended
    ) AS run_minutes
FROM batch_jobs

DATE_DIFF counts complete units and discards the remainder. A 90-minute session is 1 hour in DATE_DIFF hours. Use the smallest practical unit to preserve accuracy, then convert for display.

DATE_TRUNC to month is equivalent to setting day to 1 and time to 00:00:00. The result is a valid timestamp you can use directly in GROUP BY to create monthly aggregations.

Convert between timestamps and epoch time

FROM_UNIXTIME converts a Unix timestamp to a human-readable timestamp. TO_UNIXTIME converts a timestamp to its Unix representation. These functions bridge between application data formats and human-friendly display.

FROM_UNIXTIME takes the integer and returns a timestamp. The result is in the database's local timezone unless otherwise configured. This makes Unix timestamps human-readable for analysis.

SELECT
  ___(___) AS log_time
FROM server_logs

FROM_UNIXTIME converts in the database server's local timezone. If the server is not in UTC, the human-readable result will be offset from the original UTC epoch. Use AT TIME ZONE after conversion to get UTC.

JavaScript timestamps are milliseconds since epoch (13 digits). Dividing by 1000 before FROM_UNIXTIME is a common and necessary step when processing data from web event streams or browser logs.

Aggregate data by week, month, or quarter

Combining EXTRACT with GROUP BY enables time-based aggregations: monthly revenue trends, hourly traffic patterns, day-of-week analysis. Extracted integers become grouping keys, turning timestamp data into actionable summaries.

Both the SELECT and GROUP BY must include the same EXTRACT expressions. The result shows one row per year-month combination with aggregated metrics.

EXTRACT also works in WHERE clauses to filter by time components:

SELECT
  ___(___) AS epoch
FROM event_data

Combining EXTRACT with GROUP BY is the standard pattern for time-series reporting. The extracted integer serves as a compact grouping key that sorts naturally and is easy to filter with numeric comparisons.

Filtering with EXTRACT in WHERE bypasses date-range index pruning. Instead of WHERE EXTRACT(YEAR FROM order_date) = 2024, prefer WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'.

EXTRACT-based grouping and TO_UNIXTIME together give you two complementary tools: extract components for aggregations within a system, and convert to Unix time for compact numeric output to external APIs.