Oracle TO_DSINTERVAL function

Description

The TO_DSINTERVAL() function is used to convert a character string of (CHAR, VARCHAR2, NCHAR, or NVARCHAR2) datatype to an INTERVAL DAY TO SECOND type.

The function accepts argument in one of the two formats:

• SQL interval format compatible with the SQL standard (ISO/IEC 9075:2003)
• ISO duration format compatible with the ISO 8601:2004 standard

Uses of Oracle TO_DSINTERVAL() Function

• Interval Conversion: Convert character strings to time intervals for day-to-second precision.


• Date-Time Calculations: Perform arithmetic operations on date and time values using intervals.


• Data Normalization: Standardize interval data across different formats for consistent processing.


• Duration Representation: Represent precise time durations in reports or analyses.


• Query Filtering: Use intervals to filter and retrieve data based on specific time-based conditions.

Syntax: SQL interval format

[+ | -] days hours : minutes : seconds [. frac_secs ]

In the SQL format:

• days is an integer between 0 and 999999999.
• hours is an integer between 0 and 23.
• minutes and seconds are integers between 0 and 59.
• frac_secs is the fractional part of seconds between .0 and .999999999.
• One or more blanks separate days from hours.
• Additional blanks are allowed between format elements.

Syntax: ISO duration format

[-] P [days D] 
T [hours H] [minutes M] [seconds [. frac_secs] S]

In the ISO format:

• days, hours, minutes and seconds are integers between 0 and 999999999.
• frac_secs is the fractional part of seconds between .0 and .999999999.
• No blanks are allowed in the value.

Applies to:
Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

Example: Oracle TO_DSINTERVAL function

The following SQL query selects the employees who had worked for the company for at least 200 days on January 1, 2005 :

SQL> SELECT employee_id, last_name FROM employees
  2     WHERE hire_date + TO_DSINTERVAL('200 00:00:00')
  3     <= DATE '2005-01-01'
  4     ORDER BY employee_id;

Sample Output:

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        100 King
        102 De Haan
        108 Greenberg
        109 Faviet
        114 Raphaely
        115 Khoo
        122 Kaufling
        133 Mallin
        137 Ladwig
        141 Rajs
        156 King
...		

Previous: TO_TIMESTAMP_TZ
Next: TO_YMINTERVAL