Interval data Type-007

The interval data type is used to store the time interval between two timestamps. You can specify the spacing between years and months, or days,hours,minuts,seconds.

Oracle supports two types of inteval, which are year to month and day to SECOND. Each type contains leading field and trailing field. The primary parameter defines the date or time to be computed, and the secondary parameter defines the minimum amount of growth.

The following is an example to introduce the year to month and day to SECOND.

First, INTERVAL year to MONTH

The syntax for declaring interval year to month is:

which

1 ' integer[-integer], specifying specific values for the leading field and the optional trailing field. At the same time, the range of month that trailing field is 0 to 11.

2 percision, or precision, is the limit on the maximum length of the values in the leading field. The value range is 0-9, and the default value is 2.

Code One:

Declare
V_inteval INTERVAL Year (3) to MONTH;
Begin
V_inteval: = INTERVAL ' 123-2 ' to MONTH;
Dbms_output.put_line (V_inteval);
End

The result is: +123-02.

Description: 123 years 2 months. In this example, you must specify year, the precision of the leading field (Percision), because the default value is 2, and if you modify the declaration section to V_inteval INTERVAL year to MONTH, you will be prompted for a small error. In addition, it is important to note that the declaration part needs to be annotated with precision, but the assignment portion, that is, v_inteval: = INTERVAL ' 123-2 ' to MONTH, does not require annotation precision, and if the callout prompts for a syntax error. In this case, if the precision is modified to 4, the result is +0123-02.

Code two:

Select Inteval ' 1234 ' (4) from dual;

The result is: +1234-00.

Description: 1234 years 0 months. Because one of the inteval types, INTERVAL year to MONTH, the year (leading field) and MONTH (trailing field) must exist at the time of the Declaration, otherwise the error message is prompted. So, if we quote year or month alone, the example of code two cannot be compiled, only through SQL statements.

Similar examples are:

Select INTERVAL ' 345 ' MONTH (3) from dual;

The result is: +28-09.

Second, INTERVAL Day to SECOND

The syntax for declaring interval day to second is:

which

1 integer Specifies the number of days, the value can not exceed leading_percision.

2 time_expr The specified format is Hh[:mi[:ss[.n]]]or MI[:SS[.N]] or SS[.N], n is the decimal part of the second, and N cannot be greater than fractional_seconds_precision. and is limited by the number of digits in the fractional_seconds_precision. If leading field is day, you can select the mode of Integer time_expr.

3 Leading_precision is the Leading_field precision, the value range is 0-9, the default value is 2.

4 Fractional_seconds_precision is a value range of 1-9 for seconds, and the default value is 6.

The range of the value range of 5 hour is 0-23,minute range of 0-59,minute is 0-59.999999999.

Code One:

Declare
V_inteval INTERVAL Day (7) to SECOND (5);
Begin
V_inteval: = INTERVAL ' 4 5:12:10.222 ' to SECOND;
Dbms_output.put_line (V_inteval);
End

The result is: ++0000004 05:12:10.22200.

Description: Represents 4 days 5 hours 12 minutes 10.222 seconds. In this example, day and second precision are optional, similar to the interval year to MONTH, the declaration part needs to callout precision, but the assignment part, does not need the annotation precision, if the annotation, will prompt the syntax error.

Code two:

Select INTERVAL ' 4 5:12 ' to MINUTE from dual;

The result is: +04 05:12:00.

Description: Represents 4 days 5 hours 12 minutes 0 seconds. Because one of the inteval types, INTERVAL day to SECOND, the day and SECOND must exist at the time of the Declaration, otherwise the error message is prompted. So, if we quote year or month alone, the example of code two cannot be compiled, only through SQL statements.

Similar examples are:

Select INTERVAL ' 5 ' Day (3) to HOUR from dual;
Select INTERVAL ' (3) from dual;
Select INTERVAL ' 11:12:10.2222222 ' HOUR to SECOND (7) from dual;
Select INTERVAL ' 11:20 ' HOUR to MINUTE from dual;
Select INTERVAL ' HOUR from dual;
Select INTERVAL ' 10:22 ' MINUTE to SECOND from dual;
Select INTERVAL ' MINUTE from dual;
Select INTERVAL ' HOUR ' (3) from dual;
Select INTERVAL ' 30.12345 ' SECOND (2,4) from dual; (note that the result is +00 00:00:30.1235, if the precision is 1, 3, the result is +0 00:00:30.123, if the precision is 3, 7, the result is +000 00:00:30.1234500)