Oracle – Interval Datatype – Extracting Components (Years/Months/Days/Hours/Minutues/Seconds)- Usage Example Reference

If you have not checked my quick intro to the Interval datatype here, please do so before reading on.

Now, that we know the “interval” data type, let us see how we can extract out the individual components within it for both the “DAY TO SECOND” and “YEAR TO MONTH” variations.

The following example should be self-explanatory. Notice that the “sample_data” aliased SQL in the WITH clause is used again in the second SELECT within the same WITH clause. Although this is allowed, it is not a good idea to do so in Oracle 9i since issues have been encountered when nesting this way.

We use the EXTRACT keyword to extract individual components. The valid values that can be extracted are YEAR, MONTH, DAY, HOUR, MINUTE and SECOND besides other things. The SQL below hard-coded null values for things that cannot be extracted for the specific interval type.

For example in a “DAY TO SECOND” interval type, “YEAR” cannot be extracted. Oracle will throw an error ([Error] Execution (62: 26): ORA-30076: invalid extract field for extract source).

WITH
sample_data
AS
(
    SELECT
        --Pick some end-date and subtract from another start-date and get the interval (of type DAY TO SECOND)
        (
            (TO_DATE('12/11/2012 14:31:45', 'mm/dd/yyyy hh24:mi:ss')) /* end_time*/
            -       /* minus */
            (TO_DATE('11/29/2012 13:30:45', 'mm/dd/yyyy hh24:mi:ss')) /*start_time */
        ) DAY TO SECOND AS days_intrvl,
        --From current date and time, subtract out 500.25 days and get the interval (of type YEAR TO MONTH)
        ((sysdate) - (sysdate-970.25)) YEAR TO MONTH AS years_intrvl
    FROM
        dual
),
extract1_demo
AS
(
    SELECT
        --Get individual components of "DAY TO SECOND" interval
        'Extract from DAY TO SECOND interval data type' AS description,
        TO_CHAR(days_intrvl) interval,
        CAST(NULL AS NUMBER) AS years,
        CAST(NULL AS NUMBER) AS months,
        EXTRACT(DAY FROM days_intrvl) days,
        EXTRACT(HOUR FROM days_intrvl) hours,
        EXTRACT(MINUTE FROM days_intrvl) minutes,
        EXTRACT(SECOND FROM days_intrvl) secs
    FROM sample_data
),
extract2_demo
AS
(
    SELECT
        --Get individual components of "YEAR TO MONTH" interval
        'Extract from YEAR TO MONTH interval data type' description,
        TO_CHAR(years_intrvl) interval,
        EXTRACT(YEAR FROM years_intrvl) years,
        EXTRACT(MONTH FROM years_intrvl) months,
        CAST(NULL AS NUMBER) AS days,
        CAST(NULL AS NUMBER) AS hours,
        CAST(NULL AS NUMBER) AS minutes,
        CAST(NULL AS NUMBER) AS secs
    FROM sample_data
)
---------------------
--Output the results
---------------------
SELECT * FROM extract1_demo
UNION ALL
SELECT * FROM extract2_demo

The output for the above is:

DESCRIPTION INTERVAL YEARS MONTHS DAYS HOURS MINUTES SECS
Extract from DAY TO SECOND interval data type +000000012 01:01:00 12 1 1 0
Extract from YEAR TO MONTH interval data type +000000002-08 2 8

In a future post, we will explore the “TIMESTAMP” data type which is closely related to the INTERVAL data type.

Hopefully, you can use this as a quick reference every time you (me too) need to extract components! As always, comments and suggestions are welcome.

Advertisements

One thought on “Oracle – Interval Datatype – Extracting Components (Years/Months/Days/Hours/Minutues/Seconds)- Usage Example Reference

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s