In Oracle, an interval represents a period of time between two particular dates or date time points. It is a built-in data type used to store and manipulate durations. While you can directly add/subtract date values, I find it easier and clearer to use intervals in some situations. For example, suppose that you want to figure out 5 months ahead from today:
When performing arithmetic operations between dates or datetime values, the default unit is days. To add 5 months from the current day, it is necessary to account for the varying number of days in each month. For example, in the duration between the current day (16-Jan-2024) and 5 months ahead, February has 29 days, March has 30 days, etc. The number of days in February also depends on the year, so that there are 29 days in February 2024, whereas there are 28 days in February 2025. Therefore, specifying the unit of the interval in the operation is advisable to ensure accuracy.
INTERVAL YEAR(n) TO MONTH
One of the interval data type available in the Oracle Database is INTERVAL YEAR TO MONTH. This data type stores a duration defined by year and month values, where n represents the number of digits used for the YEAR value. The acceptable range for n is 0 to 9. This can represent a fairly high amount, nine figures of years (999,999,999 years). If omitted, n defaults to 2. Here is an example:
In the figure shown above, there is a column named my_interval specified interval year range of 3. Thus, the column can take any year intervals ranging from -999 to 999. Then in the line 6, I inserted '123-11' which indicates 123 years and 11 months. When it is represented in the data table, we got plus 123 hyphen 11. This doesn't mean 123 minus 11. Rather it means 123 years and 11 months.
Negative values are applicable to intervals as well. In line 12, I inserted '-23-11'. This represents a duration of minus 23 years and 11 months, equivalent to 287 months ago. Again, the - sign between the two numbers is not a minus sign; the only minus sign is at the beginning. You can also have intervals just expressed in terms of years or months. In line 18, we have 1123 months, which is equivalent to 93 years and 7 months.
INTERVAL DAY(n1) TO SECOND(n2)
Another interval available in Oracle is called INTERVAL DAY TO SECOND. This data type stores a time span defined in days, hours, minutes, and seconds, with precision for both days (n1) and seconds (n2). The valid range for n1 is 0-9, with a default setting of 2, determining the number of digits allowed when specifying the size of DAY component. On the other hand, the value for n2 represents the fractional seconds precision for the SECOND component, with an acceptable range of 0-9 and default value of 6.
You would find the INTERVAL DAY TO SECOND data type useful when there is a need to capture the time difference between two date values. For example:
In line 4, I specified n1 as 3. This means that the acceptable number of days is ranging from -999 to 999 days. In the same code line, I also specified n2 as 6. This means the fractional seconds will have decimal precision up to 0.000001. Then I inserted 5 different values into the table. First value I inserted is '1 2:3:4.567'. This represents 1 day, 2 hours, 3 minutes, 4.567 seconds. In the representation shown in the query result tab, we see that it is represented as +01 02:03:04.567000.
When inserting a value, we're not necessarily have to go all the way to the fractional seconds; we can just go through minutes or hours. For example, in the line 11, I inserted 3 days, 4 hours, and 5 minutes. However, Oracle will store up to the fractional seconds according to the pre-specified n1 and n2 arguments.
Similarly, we can also input values specified in a single unit of day, hour, minutes, or second. For example, in the line 20, I inserted 1123 minutes to the INTERVAL DAY TO SECOND column. Then Oracle will automatically calculate the number of days, hours, and minutes from the input value.
Interval Functions
One of the most frequent operations involving time intervals would be subtraction. For example:
In the example above, when subtracting TIMESTAMP values from another TIMESTAMP, we see that Oracle returns the resulting values as intervals. You can also subtract intervals from each other. For example:
In the figure shown above, subtracting 8 months from 6 years results in 5 years and 4 months. Similarly, when we subtract negative 1123 minutes from 1 day, 2 hours, 3 minutes, and 4.567 seconds, we got 1 day, 20 hours, 46 minutes, and 4.567 seconds.
To convert a character string to an interval, you can use TO_YMINTERVAL and TO_DSINTERVAL functions. The TO_YMINTERVAL takes a string 'y-m', where y represents years and m represents months. Then it transforms the string into INTERVAL YEAR TO MONTHS data type. For example:
Similarly TO_DSINTERVAL converts a character string in the format required for the INTERVAL DAY TO SECOND data type, which is 'DAYS HH24:MI:SS.FF'. For example, '15 14:05:10.001' is the INTERVAL DAY TO SECOND representation for 15 days, 14 hours, 5 minutes, and 10.001 seconds. For example:
You may also need to convert a numeric value into a time interval. Oracle has NUMTOYMINTERVAL and NUMTODSINTERVAL functions for the purpose. The NUMTOYMINTERVAL(n, interval_unit) converts date information in numeric form into an interval value of time. Here, n is the numerical value that you want to convert (required) and interval_unit (required) is either 'YEAR' or 'MONTH'. For example:
Similarly, the NUMTODSINTERVAL(n, interval_unit) converts date information in numeric form (n, required) into an interval value of time (interval_unit, required). The interval_unit can be one of 'DAY', 'HOUR', 'MINUTE', or 'SECOND'. For example:
0 Comments