Oracle Interval_year datatype

by
Using the interval year datatype in sql developer tool

In Oracle Database, there is a wide range of data types available to store different types of data. One such datatype is Interval year.

A new datatype named INTERVAL YEAR was added to Oracle Database 21c and is intended to record time periods expressed in years. Applications like financial reporting and project management that require the storage and manipulation of time periods that are measured in years can benefit from this data type.

Introduction to Oracle Interval year Datatype

The bigger INTERVAL group of datatypes, which also contains INTERVAL DAY and INTERVAL SECOND, includes the datatype INTERVAL YEAR. These data types are intended for storing time intervals expressed in days or seconds, respectively.

Like the other INTERVAL datatypes, the INTERVAL YEAR datatype also has a fixed-length binary representation that may be utilised in expressions and calculations as well as storage in database columns.

Also, check: Alter Table Add Column in Oracle

Oracle Interval year datatype Syntax

The syntax for creating a column with the INTERVAL_YEAR datatype in Oracle Database 21c is as follows:

column_name INTERVAL YEAR [(precision)] TO MONTH

Where:

  • column_name: is the name of the column you are creating.
  • precision: is an optional parameter that specifies the number of digits to the right of the decimal point. The maximum precision is 9.

Oracle Interval year datatype Example

Here is an example of creating a table with an INTERVAL_YEAR column:

CREATE TABLE employees (
   employee_id NUMBER(10),
   name VARCHAR2(100),
   hire_date DATE,
   length_of_service INTERVAL YEAR(2) TO MONTH
);

This creates a table called employees with columns for employee_id, name, hire_date, and length_of_service. The length_of_service column is of the INTERVAL_YEAR datatype, with a precision of 2 (meaning it can store up to two digits to the right of the decimal point).

You can then insert data into the table using the INTERVAL_YEAR datatype,

INSERT INTO employees (employee_id, name, hire_date, length_of_service)
VALUES (1, 'John Doe', TO_DATE('01-01-2010', 'DD-MM-YYYY'), INTERVAL '11-2' YEAR TO MONTH);

This inserts a row into the employees table with an employee_id of 1, a name of ‘John Doe’, a hire_date of January 1st, 2010, and a length_of_service of 11 years and 2 months.

You can also perform calculations on INTERVAL_YEAR columns, such as adding or subtracting intervals. For example, to find all employees who have been with the company for at least 10 years, you can use the following SQL query.

Using the interval_year datatype in oracle 21c
Using the interval_year datatype in oracle 21c

This will return all employees in the employees table who have been with the company for at least 10 years.

Now we will use the Interval Year datatype in a SQL developer tool using oracle 21c.

Using the interval year datatype in sql developer tool
Using the interval year datatype in the SQL developer tool

Read: How to Delete Duplicate Rows in Oracle

Features of Oracle Interval year Datatype

  • Data storage: The fixed-point decimal value representing the length of time in years and months is stored in the INTERVAL YEAR data type.
  • Range: The INTERVAL YEAR data type has a minimum and maximum value of -999999999-11 and 999999999-12, respectively.
  • Precision: The INTERVAL YEAR data type has a precision of 2, meaning that it can contain months with up to two digits of precision.
  • Arithmetic operations: The INTERVAL YEAR data type supports arithmetic operations with other INTERVAL YEAR data types as well as with the DATE and TIMESTAMP data types, including addition and subtraction.
  • Comparison operations: The INTERVAL YEAR data type enables comparison operations with other INTERVAL YEAR data types as well as with DATE and TIMESTAMP data types, including greater than, less than, and equal to.
  • Compatibility: The INTERVAL YEAR data type is compatible with other databases, such as MySQL, PostgreSQL, and SQL Server, which also have similar data types for representing the duration of time.

Advantages of Oracle Interval year Datatype

The INTERVAL YEAR data type in Oracle 21c offers several advantages over other data types for representing a duration of time. Some of these advantages include:

  • Compact storage:  The INTERVAL YEAR data type keeps track of time in a straightforward manner by storing only the years and months as fixed-point decimal numbers. As a result, it takes up less space than other data types that store the complete date and time, such DATE and TIMESTAMP.
  • Calculations with a high degree of precision are possible thanks to the INTERVAL YEAR data type. These calculations use time duration. It is appropriate for use in financial and scientific applications since it can handle fractional years and months numbers with accuracy.
  • Standardization: Because the INTERVAL YEAR data type is a standard SQL data type, many database management systems support it. This facilitates the porting of software across many platforms and  to integrate with other systems.
  • Readability: The INTERVAL YEAR data type uses a simple and intuitive format to represent a duration of time, making it easy to read and understand. It can also be displayed in various formats to suit different user preferences.

Also, check: Oracle Sdo_Geometery Datatype

Disadvantages of Oracle Interval year Datatype

While the INTERVAL YEAR data type in Oracle 21c offers several advantages, there are also some potential disadvantages to consider:

  • Limited range: The INTERVAL YEAR data type can only represent a certain set of values. It can express intervals up to 9999 years and 11 months, specifically. For some applications that demand longer intervals, this might not be enough.
  • While being able to represent fractional years and months, the INTERVAL YEAR data type’s precision is constrained by the number of digits listed in its description. For some applications that need highly accurate time computations, this might not be enough.
  • Although being a common SQL data type, not all database management systems may be able to support the INTERVAL YEAR data type. This can make it harder for applications that use this data format to be portable.
  • Conversion issues: When working with the INTERVAL YEAR data type, there may be issues with converting between different units of time, such as years, months, days, and hours. This could lead to errors or inaccuracies in time calculations.

Conclusion:

So, in this Oracle tutorial, we understood how to define and use the INTERVAL YEAR Datatype in Oracle Database. And we have also covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.