Effective data management requires accurate data capture, storage, processing, and analysis. Date and time values are critical in organizing and filtering data, providing a foundation for efficient data processing. Oracle’s EXTRACT
function helps you obtain specific data/time value components within the Oracle database.
The function facilitates precise calculations for data/time values, which can be used for conditioning, filtering, and comparing values to perform correct data analysis. With the EXTRACT
function, you can streamline your organization’s workflows and enhance operational efficiency.
In this article, you will learn how to use the EXTRACT
from Oracle function to get specific date and timestamp values from date values.
Understanding EXTRACT Function in Oracle
The EXTRACT
function draws a portion of the date/time value, such as the day of a week, the month of the year, or the hour value from the date. It is useful when you want to group or filter data from different timestamps to perform computational operations. For instance, you can use the EXTRACT
function to extract month from date in Oracle to know how many sales occurred in a particular month.
Syntax:
EXTRACT (date_component FROM expression)
Parameters
date_component:
It is the part you want to draw from the date/time value, such as day, month, hour, minute, second, or year.
expression:
It is the date/time value from which the date component is retrieved.
Examples of Using the Function EXTRACT From Oracle
The EXTRACT
function is versatile and lets you pull specific components from a given date. Here are some of the examples:
Example 1: Using EXTRACT Function to Draw Month from a Date Column
You can use the EXTRACT
function to draw the month portion from a specific date within the data column. This can be useful while creating monthly sales reports or checking employee attendance records.
The query runs on the Oracle table and selects the month from the specific date.
SELECT EXTRACT(MONTH FROM DATE ‘2024-02-24’)
FROM tablename;
Result of the query:
2
Example 2: Using EXTRACT Function to Draw Hour from a Date Column
You can use the EXTRACT
function to draw the time portion from the date or timestamp value. It can help you analyze the time when most orders are placed, such as in the morning, afternoon, or evening, and optimize your marketing strategy accordingly.
The query runs on the Oracle table and selects the hour from the specific date.
SELECT EXTRACT(HOUR FROM DATE ‘order_date’)
FROM tablename;
Example 3: Using EXTRACT Function to Check Employee Record
You can use the EXTRACT
function to check the number of employees who joined your company in a particular year.
The query runs on the Oracle table and selects all the employees hired after 2007.
SELECT last_name, employee_id, hire_date
FROM tablename
WHERE EXTRACT(YEAR FROM (hire_date, 'DD-MON-RR')) > 2007
ORDER BY hire_date;
Result of the query:
LAST_NAME EMPLOYEE_ID HIRE_DATE
------------------------- ---------
emp 1 179 04-JAN-08
emp 2 199 13-JAN-08
emp 3 164 24-JAN-08
Example 4: Using EXTRACT Function to Count Orders Placed in a Month
You can use the EXTRACT
function to count the orders placed in a particular month. This helps you keep track of the sales.
You can run a query on the Oracle database table to get the number of orders placed each month. The GROUP BY
function aggregates all the rows together with the same month name, and the ORDER BY
function sorts the counts in descending order.
SELECT EXTRACT(month FROM order_date) "Month", COUNT(order_date) "No. of Orders"
FROM tablename
GROUP BY EXTRACT(month FROM order_date)
ORDER BY "No. of Orders" DESC, "Month";
Result of the query:
Month No. of Orders
---------- -------------
11 15
6 14
7 14
3 11
5 10
2 9
9 9
8 7
10 6
1 5
12 4
4 1
How to Use Oracle PL/SQL EXTRACT Function
PL/SQL is a procedural extension for SQL and Oracle databases. It allows you to write complex scripts that help you build reliable database applications. To use the function EXTRACT
in Oracle SQL within PL/SQL, you can follow a similar approach as you would with SQL Oracle EXTRACT
.
For example, let’s assume your Oracle database has a table named employees. You want to find out which employees were hired in 2023. You must extract the year from the hire date and filter employees accordingly. Run the following command using the Oracle EXTRACT
function.
SELECT name, hire_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = 2023;
In the above code:
EXTRACT(YEAR FROM hire_date)
will extract the year part from the hire date.
- The
WHERE
clause will filter the employees hired in 2023.
Is There a Difference between the Functionality of EXTRACT Oracle SQL and EXTRACT Oracle PL/SQL
The EXTRACT
function in ORACLE SQL and PL/SQL is the same in terms of syntax and functionality; the only difference is how you use the function. The Oracle SQL EXTRACT
function is used directly within SQL queries, while you can use Oracle PL/SQL EXTRACT within the procedural blocks.
Let’s look at an example:
- Using the EXTRACT function within Oracle SQL
SELECT EXTRACT(MONTH FROM DATE ‘2O24-06-20’)
FROM tablename;
The EXTRACT
function is used with a SELECT
statement in the above query. The code will extract the year from the given date, 2024.
- Using the EXTRACT function within Oracle PL/SQL
SELECT SYSDATE AS CURRENT_DATE_TIME,
EXTRACT (YEAR FROM SYSDATE) AS ONLY_CURRENT_YEAR
FROM tablename;
In the above query, the EXTRACT
function is used within the Oracle PL/SQL procedural block to extract the year from the current date. You can use it to obtain the year part from a date/time value.
Result:
CURRENT_DATE_TIME | ONLY_CURRENT_YEAR |
20-06-2024 10:45:54 | 2024 |
Considerations While Using Function EXTRACT from Oracle
Remember the following considerations while using the EXTRACT
from Oracle function to ensure the result is accurate.
- Data Type: The data type expression used with the function should be compatible with the requested date or time component. For instance, if you want to extract the month portion from a date, you must use the DATE data type.
- Timezone: When you want to extract a time portion from a specific date/time value, consider the difference in time zones. It is an important aspect that helps you determine the offset from UTC and can affect your result.
How Hevo Helps Overcome the Challenges Associated with EXTRACT Function in Oracle
Hevo is a real-time ELT data integration tool that helps you streamline the data processing task in Oracle, reflecting changes through its flexible, no-code, and automated data pipeline. It enables you to standardize your data formats according to UTC standards before loading them into the targeted system, ensuring data consistency across the organization.
Let’s see how Hevo helps overcome challenges associated with EXTRACT
from Oracle using its built-in features:
- Data Transformation: Hevo lets you transform your data before loading it to the desired destination. It offers two types of transformations: Python-based and drag-drop. This feature allows you to standardize the time zone while data processing, ensuring your source data is aligned and consistent.
- Automated Schema Mapping: The automated schema mapping feature replicates the source data’s schema in Oracle. It eliminates the need to map every date and time field manually. This ensures the schema is correctly loaded in Oracle to perform accurate date/time calculations using the
EXTRACT
function.
- Incremental Data Loading: This feature of Hevo lets you capture and load only the modified data into your Oracle database. It means you don’t have to upload the data sets repeatedly, ensuring you get the current data to perform queries and get accurate results.
- Historical Data Syncing: The historical data syncing in Hevo allows you to go back in time by syncing data between source and destination. It also helps you compare data from different periods using the
EXTRACT
function.
Conclusion
EXTRACT from Oracle is a robust function that enhances data management within Oracle databases by allowing you to operate on date/time values. It lets you extract a portion from date or timestamp values, helping you organize, compare, and analyze your organization’s data. You can work this function directly with SQL statements or within the procedural blocks in PL/SQL statements.
Although the function is versatile and flexible, you can use ELT tools like Hevo to further optimize performance accuracy in data processing tasks. Hevo streamlines your operations for the EXTRACT function by standardizing the time zone according to UTC. It ensures the data is consistent and reliable across all the systems within your organization.
Sign up here for a 14 day free trial with Hevo!
FAQ
Q1. Can you extract only the year portion from the selected date values using an SQL query?
You can extract only the year portion from the selected date values using the below SQL query:
SELECT EXTRACT(YEAR FROM column_name) FROM tablename;
Q2. What are some of the use cases of the EXTRACT function?
Some of the use cases for the EXTRACT
function are:
- Extracting the date/time portion from a specific date for reporting and analysis purposes.
- To perform accurate date comparisons for different time frames.
- Filtering data for a specific date or time to analyze trends or patterns.
Q3. How do you get a specific time zone using the EXTRACT function?
You can use the following code to get a specific time zone using the EXTRACT
function:
SELECT EXTRACT(TIMEZONE_HOUR FROM TIMESTAMP ‘2024-06-20 11:21:34’)
FROM tablename;
Dimple is an experienced Customer Experience Engineer with four years of industry proficiency, including the last two years at Hevo, where she has significantly refined customer experiences within the innovative data integration platform. She is skilled in computer science, databases, Java, and management. Dimple holds a B.Tech in Computer Science and excels in delivering exceptional consulting services. Her contributions have greatly enhanced customer satisfaction and operational efficiency.