SQL EXTRACT
What is SQL EXTRACT?
The SQL EXTRACT function is used to extract specific date and time components (e.g., year, month, day, hour, minute) from a date or timestamp value. It allows you to access and work with individual parts of a date or time for various date and time operations in SQL.
When you would use it
You would use the EXTRACT function when you need to:
-
Date and Time Analysis: Extract and analyze specific date or time components from a date or timestamp to perform calculations or make comparisons.
-
Data formatting: Format date and time values for display or reporting by extracting the desired components.
-
Date-Based Filtering: Filter records based on specific date or time criteria by extracting and comparing individual components.
Syntax
The syntax for the EXTRACT function may vary slightly depending on the database system, but a general representation is as follows:
EXTRACT(field FROM source)
field: The date or time component you want to extract (e.g., YEAR, MONTH, DAY, HOUR, MINUTE, SECOND).source: The date or timestamp value from which you want to extract the component.
Parameter values
-
field: The date or time component you want to extract. It can be one of the following:YEAR: Extract the year.MONTH: Extract the month.DAY: Extract the day of the month.HOUR: Extract the hour.MINUTE: Extract the minute.SECOND: Extract the second.
-
source: The date or timestamp value from which you want to extract the specified component.
Example query
Here's an example SQL query that uses the EXTRACT function to retrieve the year, month, and day from a "order_date" column:
SELECT
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;
Example table response
The example query extracts the year, month, and day components from the "order_date" column in the "orders" table. The resulting table response will look like this:
| order_year | order_month | order_day |
|---|---|---|
| 2023 | 10 | 19 |
| 2023 | 9 | 5 |
| 2023 | 8 | 15 |
Use cases
-
Date and Time Analysis: To analyze date and time components for tasks like calculating age, finding the number of days between two dates, or identifying events that occurred during a specific month.
-
Data formatting: To format date and time values for display or reporting, such as showing the date and time in a customized way.
-
Date-Based Filtering: For filtering and retrieving records based on specific date or time criteria, like fetching all orders placed in a particular month.
SQL Languages Availability
The EXTRACT function is available in various SQL-based relational database management systems (RDBMS), but its implementation and syntax may differ among systems. Here are some examples of its availability:
- PostgreSQL:
EXTRACT(field FROM source) - MySQL:
EXTRACT(field FROM source) - Oracle Database:
EXTRACT(field FROM source) - Microsoft SQL Server:
DATEPART(field, source) - IBM Db2:
EXTRACT(field FROM source) - SQLite:
strftime(format, source, modifier)
Be sure to consult the documentation of your specific RDBMS for precise usage details and variations.
