Oracle’s INTERVAL Datatype Basics & Usage Examples

Oracle “Interval” data type is a new data type that was introduced in version 9i.

In simple, terms, prior to 9i, if one needed to find the “duration” between a start time and end time, the query syntax was not too awkward but results were not human friendly.

Without INTERVAL’s help

In the example below, we take a sample “start time” and an “end time”. We want to see the duration between the two in days/hours/mins. The syntax is straight forward since Oracle natively supports arithmetic on date data types. Subtracting two date columns, results in a number representing the number of days between the two dates. It could be a fraction, say .25 which translates to ¼ of 1 day = 6 hours. The burden of doing the math to convert the day into more meaningful pieces was upon us and sometimes susceptible to subtle errors.

WITH sample_data
AS
(
    SELECT
        TO_DATE('11/29/2012 13:30:45', 'mm/dd/yyyy hh24:mi:ss') start_time,
        TO_DATE('11/29/2012 14:31:45', 'mm/dd/yyyy hh24:mi:ss') end_time
    FROM DUAL
)
SELECT
    start_time,
    end_time,
    (end_time - start_time) AS duration_in_days,
    (end_time - start_time) * 24 AS duration_in_hours,
    (end_time - start_time) * 24 * 60 AS duration_in_mins
FROM sample_data

START_TIME	END_TIME	DURATION_IN_DAYS	DURATION_IN_HOURS	DURATION_IN_MINS
11/29/2012 13:30:45	11/29/2012 14:31:45	0.042361111	1.016666667	61

With a simple interval that is less than an hour, the data is not so bad but consider a case where the duration was several days + a few hours.

WITH sample_data
AS
(
    SELECT
        TO_DATE('11/29/2012 13:30:45', 'mm/dd/yyyy hh24:mi:ss') start_time,
        TO_DATE('12/11/2012 14:31:45', 'mm/dd/yyyy hh24:mi:ss') end_time
    FROM DUAL
)
SELECT
    start_time,
    end_time,
    (end_time - start_time) AS duration_in_days,
    (end_time - start_time) * 24 AS duration_in_hours,
    (end_time - start_time) * 24 * 60 AS duration_in_mins
FROM sample_data

START_TIME	END_TIME	DURATION_IN_DAYS	DURATION_IN_HOURS	DURATION_IN_MINS
11/29/2012 1:30:45 PM	12/11/2012 2:31:45 PM	12.0423611111111	289.016666666667	17341

With INTERVAL’s help

Would it not be nice to have something like this?

Duration (Days Hours:Mins:Secs.MSecs)

+12 01:01:00.000000

Above, the break up is really clear, the duration between the two dates is, 12 days, 1 hour, 1 minute and 0 seconds.

Here is exactly how it was done (with DAY TO SECOND):

WITH sample_data
AS
(
    SELECT
        TO_DATE('11/29/2012 13:30:45', 'mm/dd/yyyy hh24:mi:ss') start_time,
        TO_DATE('12/11/2012 14:31:45', 'mm/dd/yyyy hh24:mi:ss') end_time
    FROM DUAL
)
SELECT
    (end_time - start_time) DAY TO SECOND
FROM sample_data

The above statement says, convert the duration between start and end times into an INTERVAL data type that has the Days/hours/mins/seconds split.

If I were to insert the results into a table and inspect the data type on the column

CREATE TABLE MY_SCHEMA.DURATION_TEST
AS
SELECT
    --From current date and time, subtract out 100.25 days.
    ((sysdate) - (sysdate-100.25)) DAY TO SECOND AS dur
FROM DUAL

The table creation script looks like this:

CREATE TABLE MY_SCHEMA.DURATION_TEST
(
  DUR  INTERVAL DAY(0) TO SECOND(0)
)

There is a another INTERVAL data type called YEAR TO MONTH which holds the duration as the number of years followed by the number of months.

Consider this example where the time difference is 500 days and we use the YEAR TO MONTH on that duration

SELECT
    --From current date and time, subtract out 500.25 days.
    ((sysdate) - (sysdate-500.25)) YEAR TO MONTH AS dur
FROM DUAL

Duration (Years & Months)

+01-04

In the above example the duration was positive. It could also be negative (if one were to reverse the start and end dates in the expression above).

Getting the different components of the INTERVAL’s datatype

Please checkout this post where we explore the options to separate out the pieces of duration (NOT using SUBSTR).

https://sqljana.wordpress.com/2017/01/13/oracle-interval-datatype-extracting-components-yearsmonthsdayshoursminutuesseconds-usage-example-reference/