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:
|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.