Introduction to Oracle INTERVAL DAY TO SECOND Data Type

In Oracle databases, the INTERVAL DAY TO SECOND data type is used to represent a time interval between two dates or times in days, hours, minutes, and seconds. It can store a duration, such as “2 days 4 hours 30 minutes 15 seconds”.

Syntax

In Oracle, you can define a column with the INTERVAL DAY TO SECOND data type using the following syntax:

column_name INTERVAL DAY [(precision)] TO SECOND [(fractional_seconds)]

where precision represents the precision of days (default value is 2), and fractional_seconds represents the number of digits in the fractional part of seconds (default value is 0).

Use Cases

The INTERVAL DAY TO SECOND data type is commonly used to represent time intervals. It can be used in various scenarios, such as:

Calculating the time difference between two dates;
Storing the duration of an operation;
Representing the duration of a timer, etc.

Examples

Here are some examples of using the INTERVAL DAY TO SECOND data type:

Example 1

Create a table with a column of type INTERVAL DAY TO SECOND:

CREATE TABLE sample_table (
  id   NUMBER(10),
  duration INTERVAL DAY(2) TO SECOND(2)
);

Insert some data into the table:

INSERT INTO sample_table VALUES (1, INTERVAL '2 03:12:45' DAY TO SECOND);
INSERT INTO sample_table VALUES (2, INTERVAL '1 06:30:15' DAY TO SECOND);

Query the data in the table:

SELECT * FROM sample_table;

The query result will be:

ID    DURATION
----  -----------------
1     +02 03:12:45.000000
2     +01 06:30:15.000000

Example 2

Calculate the time difference between two dates:

SELECT TIMESTAMP '2022-03-25 10:00:00' - TIMESTAMP '2022-03-24 12:30:00' AS duration
FROM dual;