Oracle interval day Datatype

by
Using the interval_day 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 day.

A new data type called “interval day” is included in Oracle 21c, the most recent version of the Oracle Database. This data type has a precision of up to microseconds and represents a time interval in days. We will examine the interval day data type, its characteristics, and its application in Oracle 21c in this article.

Introduction to Oracle interval_day Datatype

A time period defined in days is represented by the interval day data type. The existing interval data type in Oracle, which specifies a length of time in years, months, days, hours, minutes, and seconds, is comparable to this new data type. The format of the interval day data type, on the other hand, is far more straightforward and merely includes the number of days and microseconds in the interval.

Also, check: Oracle numeric Datatype

Oracle interval_day datatype Syntax

Here is the Syntax for defining an interval_day data type is as follows:

INTERVAL DAY [(fractional_seconds_precision)]

The “fractional_seconds_precision” is an optional parameter that specifies the precision of the microseconds in the interval. The valid range is 0 to 9, with a default of 6.

Oracle interval_day datatype Example

To use the interval_day data type in Oracle 21c, you can define a column with this data type in a table or use it in an expression in a statement.

Here first we will use the interval_day datatype in a query

CREATE TABLE orders (
order_id NUMBER,
order_date DATE
);

In this example, we have created a table called “orders” with two columns: “order_id” and “order_date”. The “order_date” column is of type DATE.

To insert date format values into the order_date column, you can use an INSERT statement. Here is an example of how to insert a date format into the “order_date” column.

INSERT INTO orders (order_id, order_date)
VALUES (1, TO_DATE('2022-01-01', 'YYYY-MM-DD'));

INSERT INTO orders (order_id, order_date)
VALUES (2, TO_DATE('2022-02-15', 'YYYY-MM-DD'));

INSERT INTO orders (order_id, order_date)
VALUES (3, TO_DATE('2022-03-31', 'YYYY-MM-DD'));

Now we want to fetch the values from the ‘orders’ named table

select * from orders;
Using the interval_day datatype in oracle 21c
Using the interval_day datatype in oracle 21c

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

Using the interval_day datatype in sql developer tool
Using the interval_day datatype in the SQL developer tool

Now we will insert the values into the following given table

INSERT INTO employee (employee_id, hire_date, tenure)
VALUES (1, TO_DATE('2010-01-01', 'YYYY-MM-DD'), NULL);

INSERT INTO employee (employee_id, hire_date, tenure)
VALUES (2, TO_DATE('2015-05-15', 'YYYY-MM-DD'), NULL);

INSERT INTO employee (employee_id, hire_date, tenure)
VALUES (3, TO_DATE('2020-11-30', 'YYYY-MM-DD'), NULL);

In this example, we’re inserting data into the “employee” table for three employees. For each employee, we’re providing their employee ID and hire date, which is stored as a NUMBER and a DATE data type, respectively.

Since the “tenure” column is defined as an INTERVAL DAY data type, we don’t need to provide a value for it during insertion. Instead, we can calculate the tenure using the “hire_date” and the current date when we query the table later on.

insert the values into a employee table
insert the values into an employee table

Read: Real Datatype Oracle

Features of Oracle interval_day Datatype

Here are some features of the INTERVAL DAY data type in Oracle.

  • Time intervals are represented as days using the INTERVAL DAY data type, which has a precision of up to six decimal places. It offers a quick and effective method for using time-related data in Oracle.
  • Calculating time intervals is simply because of the support for arithmetic operations like addition and subtraction provided by the INTERVAL DAY data type. The difference between two dates, for instance, can be calculated and expressed as a range of days.
  • Support for NULL values: The INTERVAL DAY data type allows for the storage of partial or ambiguous data pertaining to a time interval. For instance, if you are simply aware of the event’s beginning date
  • Ease of use: The INTERVAL DAY data type is easy to use and understand. You can create and manipulate INTERVAL DAY data type columns using simple SQL statements, without the need for complex coding or programming.

Advantages of Oracle interval_day Datatype

Here are some advantages of using the INTERVAL DAY data type in Oracle.

  • Time intervals are represented as days using the INTERVAL DAY data type, which has a precision of up to six decimal places. It offers a quick and effective method for using time-related data in Oracle.
  • Calculating time intervals is simply because to the support for arithmetic operations like addition and subtraction provided by the INTERVAL DAY data type. The difference between two dates, for instance, can be calculated and expressed as a range of days.
  • Support for NULL values: The INTERVAL DAY data type allows for the storage of partial or ambiguous data pertaining to a time interval. For instance, if you are simply aware of the event’s beginning date
  • Improved data integrity: Using the INTERVAL DAY data type can help to improve data integrity by ensuring that time-related data is stored and processed consistently. This can help prevent errors and inconsistencies when working with dates and times using other data types.
  • Compatibility with other data types: The INTERVAL DAY data type is compatible with other data types in Oracle, such as DATE, TIMESTAMP, and INTERVAL YEAR TO MONTH. This means that INTERVAL DAY columns can be used in join conditions or comparisons with other columns that have compatible data types.

Disadvantages of Oracle interval_day Datatype

Here are some disadvantages of using the INTERVAL DAY data type in Oracle.

  • Limited value range: The range of values that can be stored for the INTERVAL DAY data type is relatively small. The highest amount of data that may be stored is 999,999,999 days, or around 2,739 years. If you need to work with time intervals that are longer than this range, this can be a problem.
  • Restricted precision: INTERVAL DAY’s precision is restricted to days, which may not be sufficient for all calculations. For instance, INTERVAL DAY might not be the optimal data type to employ if you need to do calculations that call for exact measurements of hours, minutes, or seconds.
  • Potential for errors: When using the INTERVAL DAY data type, it’s important to be aware of potential errors that may occur due to the way that time intervals are calculated. For example, if you’re working with time intervals that cross over daylight saving time changes or leap years, you may need to take extra care to ensure that your calculations are accurate.
  • Complexity: The syntax for working with the INTERVAL DAY data type can be more complex than other data types in Oracle, which may make it more difficult to work with for some users.

Conclusion:

So, in this Oracle tutorial, we understood how to define and use the INTERVAL DAY 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.