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_TIMEONLY_CURRENT_YEAR
20-06-2024 10:45:542024

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;
mm
Customer Experience Engineer, Hevo Data

Dimple, an experienced Customer Experience Engineer, possesses four years of industry proficiency, with the most recent two years spent at Hevo. Her impactful contributions significantly contribute to refining customer experiences within the innovative data integration platform.

All your customer data in one place.