EXTRACT() in SQL
Introduction
EXTRACT() is a SQL temporal function that returns one component from a date, time, timestamp, or supported interval value. It can retrieve a year, month, day, hour, minute, second, quarter, or another field supported by the database product.
SELECT EXTRACT(
YEAR FROM DATE '2026-09-26'
) AS year_value;
-- 2026
Extraction is useful for reporting, grouping, trend analysis, temporal classification, validation, and display-support logic. It does not return the complete original value, and it does not format the component as user-facing text. Correct use depends on understanding the source type, the selected field, time-zone context, index behavior, and differences among database systems.
Basic Syntax
EXTRACT(field FROM source)
The field identifies the component to retrieve, and the source supplies the temporal value. The FROM keyword is part of the syntax.
SELECT EXTRACT(
MONTH FROM DATE '2026-09-26'
) AS month_value;
-- 9
The result is normally numeric. September becomes 9 rather than the word September. Use a formatting or month-name function when the requirement is presentation text.
Common Temporal Fields
| Field | Typical result | Meaning |
|---|---|---|
YEAR | 2026 | Calendar year |
MONTH | 9 | Numeric month |
DAY | 26 | Day of month |
HOUR | 18 | Hour component |
MINUTE | 45 | Minute component |
SECOND | 30 | Second component |
QUARTER | 3 | Calendar quarter where supported |
Supported fields and their exact result types vary by DBMS. Fields such as day of week, day of year, epoch, and compound MySQL units are not uniformly portable.
Extracting the Year
SELECT EXTRACT(
YEAR FROM DATE '2026-09-26'
);
-- 2026
Year extraction is common in annual reports, cohort labels, partitions, and validation. It returns the year component and does not alter the source date.
Extracting the Month
SELECT EXTRACT(
MONTH FROM DATE '2026-09-26'
);
-- 9
The result is a month number. Month extraction alone does not identify a unique reporting period because every year has a month 9.
Extracting the Day
SELECT EXTRACT(
DAY FROM DATE '2026-09-26'
);
-- 26
DAY normally means day of month. It does not automatically mean day of week or day of year; those concepts need different fields or DBMS-specific functions.
Extracting Hours and Minutes
SELECT EXTRACT(
HOUR FROM TIMESTAMP '2026-09-26 18:45:30'
) AS hour_value,
EXTRACT(
MINUTE FROM TIMESTAMP '2026-09-26 18:45:30'
) AS minute_value;
-- 18, 45
The source must contain a meaningful time component. Applying time extraction to a date-only value may be unsupported, produce zero, or involve conversion rules depending on the DBMS.
Extracting Seconds and Fractional Seconds
SELECT EXTRACT(
SECOND FROM TIMESTAMP '2026-09-26 18:45:30.625'
) AS second_value;
Some systems return seconds with a fractional component when the source type preserves fractional precision. Do not assume an integer result across all products and drivers. If microsecond analysis matters, verify source precision, extraction field support, and serialization behavior.
Extracting Several Components
SELECT EXTRACT(YEAR FROM order_time) AS order_year,
EXTRACT(MONTH FROM order_time) AS order_month,
EXTRACT(DAY FROM order_time) AS order_day,
EXTRACT(HOUR FROM order_time) AS order_hour
FROM orders;
Returning separate components can support a reporting layer, but it should not replace the authoritative timestamp. Keep the original value available so consumers can reconstruct context and apply different time zones or grouping rules.
Using EXTRACT() with CURRENT_DATE
SELECT EXTRACT(YEAR FROM CURRENT_DATE) AS current_year,
EXTRACT(MONTH FROM CURRENT_DATE) AS current_month,
EXTRACT(DAY FROM CURRENT_DATE) AS current_day;
The values depend on the database or session date context. For reproducible reports, bind a stable reporting date instead of deriving components from the live clock each time.
Using EXTRACT() with CURRENT_TIMESTAMP
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) AS current_hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS current_minute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS current_second;
Current timestamp semantics and time zone depend on the DBMS and session. A globally distributed application should avoid assuming that the database session zone matches the user's location.
Extracting from a Column
SELECT employee_name,
joining_date,
EXTRACT(YEAR FROM joining_date) AS joining_year
FROM employees;
Each row produces its own component. If joining_date is NULL, the extracted year is also NULL, preserving the fact that the source value is unknown.
Order Year, Month, and Day
SELECT order_id,
order_date,
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
EXTRACT(DAY FROM order_date) AS order_day
FROM orders;
These columns are useful for exports and exploratory analysis. For long-term application interfaces, names should make it clear that month and day are numeric components rather than formatted labels.
Filtering by Year
SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026;
This is readable, but it applies a function to each candidate column value. If order_date is indexed and the table is large, a half-open range is often preferable.
SELECT *
FROM orders
WHERE order_date >= '2026-01-01'
AND order_date < '2027-01-01';
Filtering by Month
SELECT *
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 9;
This finds September records from every year. That may be correct for seasonality analysis, but it is wrong when the requirement is specifically September 2026.
Filtering by Year and Month
SELECT *
FROM orders
WHERE order_date >= '2026-09-01'
AND order_date < '2026-10-01';
The half-open range identifies one specific month, includes every timestamp on September 30, and avoids constructing a final instant such as 23:59:59.999999. It also leaves the indexed column bare.
Grouping by Year
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
COUNT(*) AS total_orders
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date)
ORDER BY order_year;
This is a natural use of EXTRACT because the function defines the reporting dimension. State the time zone used when order_date represents an instant that can fall into different local years.
Grouping by Month
SELECT EXTRACT(MONTH FROM order_date) AS order_month,
COUNT(*) AS total_orders
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY order_month;
This combines January from all years into group 1, February into group 2, and so on. It is suitable for seasonality, but not for a chronological month-by-month trend.
Grouping by Year and Month
SELECT EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
COUNT(*) AS total_orders
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY order_year, order_month;
The pair uniquely identifies a calendar month. Returning both numeric components also provides a reliable chronological sort, unlike an unpadded display label.
Monthly Sales Report
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
COUNT(*) AS order_count,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY sales_year, sales_month;
This report shows only months that contain orders. If charts must show zero for inactive months, join the aggregate to a calendar dimension containing every reporting month.
Filling Missing Reporting Periods
Grouping transaction rows cannot invent months with no data. A calendar table provides a complete series and keeps reporting rules centralized.
SELECT c.year_number,
c.month_number,
COALESCE(SUM(o.total_amount), 0) AS total_sales
FROM calendar_month c
LEFT JOIN orders o
ON o.order_date >= c.month_start
AND o.order_date < c.next_month_start
WHERE c.month_start >= :report_start
AND c.month_start < :report_end
GROUP BY c.year_number, c.month_number
ORDER BY c.year_number, c.month_number;
This pattern also supports fiscal calendars and localized labels without changing transaction timestamps.
Extracting the Hour for Activity Analysis
SELECT EXTRACT(HOUR FROM request_time) AS request_hour,
COUNT(*) AS request_count
FROM api_logs
GROUP BY EXTRACT(HOUR FROM request_time)
ORDER BY request_hour;
The result can reveal peak traffic hours, but the analysis is meaningful only in a declared time zone. UTC traffic by hour answers a different question from each customer's local-hour behavior.
Local-Hour Analysis
When timestamps are stored as UTC, convert to the required named zone before extracting the hour. The exact conversion syntax is DBMS-specific.
SELECT EXTRACT(
HOUR FROM CONVERT_TZ(
request_time,
'UTC',
'America/Chicago'
)
) AS local_hour,
COUNT(*) AS request_count
FROM api_logs
GROUP BY local_hour
ORDER BY local_hour;
MySQL time-zone tables must be populated for named-zone conversion. Daylight-saving transitions can repeat or skip local hours, so interpret hourly totals carefully.
Birthday Month
SELECT employee_name, birth_date
FROM employees
WHERE EXTRACT(MONTH FROM birth_date) = 9;
Month-only extraction is appropriate here because the year is intentionally irrelevant. If reminder scheduling depends on the current year, explicitly define how February 29 birthdays are handled in non-leap years.
Quarter Extraction
SELECT EXTRACT(
QUARTER FROM DATE '2026-09-26'
) AS calendar_quarter;
-- 3
Calendar quarters map January through March to Q1, April through June to Q2, July through September to Q3, and October through December to Q4. Field support varies by product.
Quarterly Sales
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(QUARTER FROM order_date) AS sales_quarter,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(QUARTER FROM order_date)
ORDER BY sales_year, sales_quarter;
Always include the year for chronological quarterly reporting. Grouping by quarter alone combines Q1 from every year.
Fiscal Periods Are Not Always Calendar Periods
A company fiscal year may begin in April, use 4-4-5 weeks, or include a 53rd week. Calendar YEAR, MONTH, and QUARTER extraction cannot represent every fiscal system. Join the transaction date to a governed fiscal calendar dimension containing fiscal year, period, week, and quarter.
This avoids duplicating complicated CASE expressions across reports and ensures finance, operations, and analytics use the same period definitions.
MySQL YEAR_MONTH Extraction
SELECT EXTRACT(
YEAR_MONTH FROM '2026-09-26'
) AS year_month_value;
-- 202609
YEAR_MONTH is a MySQL compound unit and is not portable standard syntax. It is compact for sorting and grouping, but separate year and month columns are often clearer for APIs and multi-database applications.
MySQL Compound Units
MySQL supports compound extraction units such as DAY_HOUR, DAY_MINUTE, DAY_SECOND, HOUR_MINUTE, HOUR_SECOND, and MINUTE_SECOND for appropriate temporal or interval-like expressions.
These units are specialized and require attention to returned numeric encoding. Prefer ordinary single fields unless compound extraction directly serves a tested requirement.
EXTRACT() and NULL
SELECT EXTRACT(YEAR FROM NULL);
-- NULL
An unknown temporal source has an unknown component. This behavior helps distinguish missing dates from real component values. Use COALESCE only when the fallback has a documented meaning.
Using COALESCE Carefully
SELECT employee_name,
EXTRACT(
YEAR FROM COALESCE(resignation_date, CURRENT_DATE)
) AS effective_end_year
FROM employees;
This treats a missing resignation date as the current date, which may be valid for an active-employment report. The alias should not be resignation_year because active employees have not resigned.
EXTRACT() Versus YEAR(), MONTH(), and DAY()
SELECT YEAR(order_date),
MONTH(order_date),
DAY(order_date)
FROM orders;
SELECT EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date),
EXTRACT(DAY FROM order_date)
FROM orders;
MySQL convenience functions are concise, while EXTRACT follows a SQL-style pattern shared by several systems. Choose a consistent project convention and consider portability requirements.
EXTRACT() Versus DATE_FORMAT()
Extraction returns a component suitable for calculation, comparison, grouping, or sorting. Formatting returns text intended for presentation.
SELECT EXTRACT(MONTH FROM order_date) AS month_number,
DATE_FORMAT(order_date, '%M') AS month_name
FROM orders;
The first result might be 9; the second might be September. Avoid grouping or sorting by localized display text when a numeric or date key is available.
EXTRACT() Versus DATEDIFF()
EXTRACT() takes one component out of a temporal value. MySQL DATEDIFF() compares two date values and returns a number of calendar days.
EXTRACT(YEAR FROM order_date) -- component
DATEDIFF(delivery_date, order_date) -- distance
Use extraction to classify or group; use a difference function to measure separation.
Extraction Versus Date Truncation
Extraction returns a number such as month 9. Date truncation, where supported, returns a temporal boundary such as the first instant of September 2026. Truncated dates often make better reporting keys because one value preserves year and month identity.
MySQL does not use the same general DATE_TRUNC syntax as PostgreSQL, so projects commonly construct boundaries, use calendar dimensions, or store generated period keys after reviewing requirements.
Index-Friendly Filtering
A predicate such as EXTRACT(YEAR FROM created_at) = 2026 may prevent a normal index on created_at from supporting a simple range scan. Prefer direct boundaries when filtering a known period.
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01'
Use EXPLAIN to verify the plan. Selectivity, table size, statistics, selected columns, partitions, and available functional indexes all influence performance.
Functional Indexes and Generated Columns
If queries frequently group or filter by the same extracted component, a supported functional index or generated column may help. The design should preserve full-period identity where necessary.
ALTER TABLE orders
ADD COLUMN order_year_month INT
GENERATED ALWAYS AS (
EXTRACT(YEAR_MONTH FROM order_date)
) STORED,
ADD INDEX ix_orders_year_month (order_year_month);
Review write cost, storage, determinism rules, time-zone dependence, and whether a direct date-range index already solves the problem more simply.
Partition Pruning
Large event tables may be partitioned by a date key. A direct range on the partitioning column often gives the optimizer clearer pruning boundaries than wrapping the column in EXTRACT. Keep filtering strategy aligned with partition definitions and verify selected partitions in the execution plan.
Stable Period Keys
Concatenating unpadded year and month text can create ambiguous or incorrectly sorted values. Use separate numeric fields, a real month-start date, or a fixed numeric key such as 202609.
SELECT EXTRACT(YEAR FROM order_date) * 100
+ EXTRACT(MONTH FROM order_date) AS year_month_key
FROM orders;
A month-start DATE is often the most expressive warehouse dimension key because it remains a temporal value.
Day of Week and Day of Year
Fields such as day of week and day of year vary in availability and conventions. PostgreSQL supports DOW and DOY; MySQL commonly uses functions such as DAYOFWEEK(), WEEKDAY(), and DAYOFYEAR().
Day-of-week numbering differs: one system may use Sunday as zero, another Sunday as one, and ISO conventions use Monday as one. Never expose an undocumented number to users or APIs.
Week Numbers and Week Years
Week reporting is more complex than extracting a week number. Dates near New Year can belong to a week-based year different from their calendar year, and systems differ in first-day and minimum-days rules.
When ISO weeks matter, store or calculate both ISO week year and ISO week number using functions verified for the DBMS. Grouping calendar YEAR with an ISO week can misclassify boundary dates.
Time Zones and Timestamp Types
An instant near midnight can have different year, month, day, and hour values in different zones. MySQL TIMESTAMP values are commonly converted according to the session time zone, while DATETIME values represent stored wall-clock fields without automatic zone conversion.
Define whether reporting uses UTC, account zone, location zone, or another business zone before extraction. A numeric offset is not a complete policy because daylight-saving and historical rules change.
Daylight-Saving Transitions
Local-hour grouping can contain a repeated hour when clocks move backward and a missing hour when clocks move forward. EXTRACT correctly returns the local component after conversion, but an hourly chart may need an offset or UTC key to distinguish repeated times.
For elapsed-duration analysis, do not infer duration from extracted hour values. Compare full instants with an appropriate difference operation.
PostgreSQL EXTRACT()
PostgreSQL supports standard-style extraction and additional fields such as DOW, ISODOW, DOY, EPOCH, WEEK, and ISOYEAR.
SELECT EXTRACT(YEAR FROM DATE '2026-09-26'),
EXTRACT(DOY FROM DATE '2026-09-26'),
EXTRACT(QUARTER FROM DATE '2026-09-26');
These extensions are useful but not automatically portable to MySQL or Oracle.
Oracle EXTRACT()
SELECT EXTRACT(
YEAR FROM DATE '2026-09-26'
)
FROM dual;
Oracle supports fields appropriate to the source type. Oracle DATE stores time information, but EXTRACT treats it according to ANSI DATE rules for supported fields; time-zone fields require appropriate timestamp-with-time-zone types. Verify type compatibility rather than assuming every stored component can be extracted with every field.
SQL Server DATEPART()
SQL Server commonly uses DATEPART() instead of EXTRACT.
SELECT DATEPART(YEAR, '2026-09-26') AS year_value,
DATEPART(MONTH, '2026-09-26') AS month_value,
DATEPART(DAY, '2026-09-26') AS day_value;
SQL Server also provides YEAR, MONTH, and DAY convenience functions. Weekday and week results can depend on settings such as DATEFIRST.
Cross-Database Comparison
| Database | Typical year extraction | Notes |
|---|---|---|
| MySQL | EXTRACT(YEAR FROM value) | Also supports YEAR() |
| PostgreSQL | EXTRACT(YEAR FROM value) | Rich field set |
| Oracle | EXTRACT(YEAR FROM value) | Field must match source type |
| SQL Server | DATEPART(YEAR, value) | Different function syntax |
Invalid Input and Implicit Conversion
Ambiguous strings and invalid dates can produce errors, warnings, NULL, or coercion depending on the DBMS and configuration. Bind typed parameters and use unambiguous temporal literals. Strict input validation prevents environment-dependent parsing.
Calculated Fields in APIs
API names should identify the component and context, such as orderYear, localRequestHour, or fiscalQuarter. A generic field named month does not say whether it is numeric, localized text, zero-based, or tied to a particular zone.
Keep the authoritative timestamp in the contract when consumers may need different local representations. Extracted values can become stale or inconsistent if generated under an undocumented zone.
Extracting from Intervals
Some databases allow EXTRACT to read components from interval values as well as dates and timestamps. PostgreSQL, for example, can expose year, month, day, hour, minute, and second components from an interval.
-- PostgreSQL example
SELECT EXTRACT(
DAY FROM INTERVAL '12 days 05:30:00'
) AS day_component;
A component is not always the total duration expressed in that unit. An interval containing one day and two hours has an hour component of two, not 26 total hours. Use epoch conversion or an appropriate duration calculation when a total unit is required, and verify interval normalization rules for the DBMS.
Seasonal Analysis Across Years
Grouping by month alone is intentional when comparing seasonality across several years. A stronger report keeps year-level observations available rather than immediately combining everything into one total.
SELECT EXTRACT(YEAR FROM sold_at) AS sales_year,
EXTRACT(MONTH FROM sold_at) AS sales_month,
SUM(amount) AS monthly_sales
FROM sale
GROUP BY EXTRACT(YEAR FROM sold_at),
EXTRACT(MONTH FROM sold_at)
ORDER BY sales_year, sales_month;
A later layer can compare each September, calculate a multi-year average, and distinguish a seasonal pattern from one exceptional year.
Labels and Chronological Sorting
Formatted labels such as "September 2026" are useful for display but unreliable as the only sort key. Alphabetical order places April before February and can mix years unexpectedly.
SELECT EXTRACT(YEAR FROM order_date) AS year_number,
EXTRACT(MONTH FROM order_date) AS month_number,
DATE_FORMAT(order_date, '%M %Y') AS month_label,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY year_number, month_number, month_label
ORDER BY year_number, month_number;
Return a machine-friendly period key and a separate display label. Let the presentation layer localize month names when users have different languages.
Per-Account Time-Zone Reporting
A multi-tenant system may need each account's local month or hour. Convert each instant using the account's validated named zone before extraction.
SELECT a.account_id,
EXTRACT(
MONTH FROM CONVERT_TZ(
o.ordered_at,
'UTC',
a.time_zone_name
)
) AS local_order_month,
COUNT(*) AS orders
FROM orders o
JOIN account a ON a.account_id = o.account_id
GROUP BY a.account_id, local_order_month;
This can be expensive at scale. A warehouse may materialize governed local-date dimensions, but it must recalculate them if the underlying zone policy changes.
Calendar Dimensions for Governed Reporting
A calendar dimension stores one row per date with calendar year, month, ISO week, fiscal period, holiday flags, season, and localized labels. Joining by date centralizes definitions that would otherwise appear as repeated EXTRACT and CASE expressions.
SELECT c.fiscal_year,
c.fiscal_period,
SUM(o.total_amount) AS total_sales
FROM orders o
JOIN calendar_date c
ON c.calendar_date = DATE(o.order_date)
GROUP BY c.fiscal_year, c.fiscal_period
ORDER BY c.fiscal_year, c.fiscal_period;
For large fact tables, warehouses commonly maintain a date key to avoid applying DATE conversion during every join.
EXTRACT() with Window Functions
Extracted periods can partition analytic calculations. The following example compares each order with the running sales total inside its calendar month.
SELECT order_id,
order_date,
total_amount,
SUM(total_amount) OVER (
PARTITION BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
ORDER BY order_date, order_id
) AS month_running_sales
FROM orders;
A deterministic secondary sort key handles orders sharing the same timestamp. For heavy analytics, a month-start key can make the partition definition shorter and easier to reuse.
Materialized Summaries and Refresh Policy
Repeated extraction and aggregation over billions of event rows may be too expensive for interactive dashboards. A summary table can store counts and amounts by governed period key.
The design needs a refresh watermark, late-arriving-event policy, correction process, and reconciliation checks against source data. Materialization improves query speed but introduces freshness and consistency responsibilities. Store the period boundary or year-month key used by the summary so its meaning remains explicit.
Extracted Components for Data Validation
Extraction can help find impossible or policy-violating temporal values after conversion. For example, a batch expected to contain only business-year 2026 events can be checked by year component, while a direct range remains preferable for the actual filter.
SELECT event_id, occurred_at
FROM imported_event
WHERE occurred_at < '2026-01-01'
OR occurred_at >= '2027-01-01';
Use EXTRACT in diagnostic output to explain the anomaly, but validate source types and zones first. An unexpected year may indicate a unit conversion error, a wrong time zone, or malformed input rather than a genuinely old event.
Migration and Conformance Testing
When moving extraction logic between databases, build a conformance set containing leap day, year end, midnight, fractional seconds, NULL, offset timestamps, daylight-saving transitions, and week-year boundaries. Compare both values and result types.
Translate nonportable fields deliberately. PostgreSQL ISODOW, MySQL YEAR_MONTH, Oracle time-zone fields, and SQL Server DATEPART options do not map one-to-one in every case. Automated comparison protects reports from silent semantic changes.
Reusing Extracted Components
When one query uses the same expression in selection, classification, and ordering, calculate it once in a common table expression.
WITH order_component AS (
SELECT order_id,
total_amount,
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month
FROM orders
)
SELECT order_year,
order_month,
COUNT(*) AS orders,
SUM(total_amount) AS sales
FROM order_component
GROUP BY order_year, order_month
ORDER BY order_year, order_month;
This makes the period definition visible in one place and reduces the chance that repeated expressions drift during maintenance.
Testing EXTRACT()
Use fixed temporal values in tests. Cover the first and last date of a year, every month boundary, February 29, midnight, fractional seconds, NULL, invalid input, time-zone conversion around midnight, daylight-saving transitions, and week-year boundaries when relevant.
For performance rewrites, prove that an EXTRACT predicate and its proposed date range return the same rows at both boundaries. Also compare execution plans.
Common Mistakes
- Writing
EXTRACT(field, source)instead of usingFROM. - Expecting EXTRACT to return the complete date.
- Expecting MONTH to return a month name.
- Assuming DAY means day of week.
- Grouping by month without year for a chronological report.
- Using calendar quarters for a noncalendar fiscal year.
- Ignoring the time zone before extracting local components.
- Filtering an indexed column with EXTRACT when a range is clearer.
- Assuming week and weekday conventions are portable.
- Using an unsupported field for the source type or DBMS.
Best-Practice Checklist
- Choose a field supported for the source type and database.
- Use numeric components for analysis and formatting for display text.
- Include year with month or quarter when period identity matters.
- Use calendar dimensions for fiscal and complete-period reporting.
- Convert instants to the authoritative zone before local extraction.
- Prefer half-open date ranges for indexed period filtering.
- Retain the original timestamp alongside extracted components.
- Document weekday, week-year, and quarter conventions.
- Use stable as-of values for reproducible reports.
- Test boundaries, NULL, time zones, precision, and portability.
Interview Questions and Answers
What does EXTRACT do? It returns one specified component from a compatible date, time, timestamp, or interval value.
How do you extract a year? Use EXTRACT(YEAR FROM value).
Does MONTH return a name? No. It normally returns a numeric month; use formatting for a name.
What does DAY mean? It normally means day of month, not weekday.
Why group by both year and month? Month alone combines the same month across all years.
EXTRACT versus DATE_FORMAT? EXTRACT returns a component for logic or analysis; DATE_FORMAT returns text for presentation.
Why can an EXTRACT filter be slower? Wrapping an indexed column can prevent a simple range scan.
What does SQL Server use? SQL Server commonly uses DATEPART().
Complete Monthly Reporting Example
WITH monthly_sales AS (
SELECT EXTRACT(YEAR FROM order_date) AS sales_year,
EXTRACT(MONTH FROM order_date) AS sales_month,
COUNT(*) AS order_count,
SUM(total_amount) AS total_sales
FROM orders
WHERE order_date >= :report_start
AND order_date < :report_end
GROUP BY EXTRACT(YEAR FROM order_date),
EXTRACT(MONTH FROM order_date)
)
SELECT sales_year,
sales_month,
order_count,
total_sales
FROM monthly_sales
ORDER BY sales_year, sales_month;
The range predicate supports efficient filtering, while EXTRACT defines the report dimensions. A production report can join this aggregate to a calendar dimension when empty months must appear.
Key Takeaway
EXTRACT() takes one meaningful component from a temporal value. It is especially useful for reporting, grouping, classification, validation, and analysis when the selected field and time-zone context are explicit.
RELIABLE EXTRACT LOGIC = APPROPRIATE SOURCE TYPE
+ SUPPORTED TEMPORAL FIELD
+ EXPLICIT TIME ZONE
+ COMPLETE PERIOD IDENTITY
+ INDEX-AWARE FILTERING
+ PORTABILITY REVIEW
+ BOUNDARY TESTS
Use extraction for temporal components, formatting for user-facing text, ranges for efficient period filters, and calendar dimensions for fiscal or complete-series reports. Those distinctions keep date analysis accurate, understandable, and portable.