Explanation of the INTERVAL function in oracle

Explanation of the INTERVAL function in oracle interval year to month data type Oracle Syntax: INTERVAL 'integer [-integer] '{YEAR | MONTH} [(precision)] [TO {YEAR | MONTH}] This data type is often used TO indicate a time difference. Note that the time difference is accurate only TO the YEAR and MONTH. precision is the exact domain of year or month, valid range is 0 to 9, default value is 2. eg: INTERVAL '2014-2 'YEAR (3) to month indicates: February 1, 123, 2 months, and "YEAR (3)" indicates that the accuracy of the YEAR is 3, it can be seen that "123" is a valid value of 3. If YEAR (n) and n <3 are there, an error occurs. Note that the default value is 2. INTERVAL '000000' YEAR (3) indicates: 123 0 months INTERVAL '000000' MONTH (3) indicates: 123 months. Note that the precision of the MONTH here is 3. INTERVAL '4' YEAR indicates 4 years, and INTERVAL '4-0' year to month indicates the same INTERVAL '50' MONTH, the value of INTERVAL '4-2' year to month is the same as that of INTERVAL '000000' YEAR, which indicates that there is an error. The value of 123 precision is 3, but the default value is 2, therefore, INTERVAL '000000' YEAR (3) or "3" TO a value greater than 3 and less than or equal TO 9 can INTERVAL '5-3 'year to month + INTERVAL '20' MONTH = INTERVAL '6-11 'year TO MONTH indicates: 5 years, 3 months, + 20 months = 6 years, 11 months, functions related to this type: NUMTODSINTERVAL (n, 'interval _ unit ') converts n to interval _ The value specified by unit. interval_unit can be: DAY, HOUR, MINUTE, and SECOND. Note that this function cannot be converted to YEAR or MONTH. NUMTOYMINTERVAL (n, 'interval _ unit ') interval_unit can be: YEAR, MONTH eg: (Oracle Version 9204, RedHat Linux 9.0) SQL> select numtodsinterval (100, 'day ') from dual; NUMTODSINTERVAL (100, 'day') --------------------------------------------------------------------- + 000000100 00:00:00. 000000000 SQL> c/DAY/SECOND1 * select numtodd Sinterval (100, 'second') from dualSQL>/NUMTODSINTERVAL (100, 'second') --------------------------------------------------------------------- + 000000000 00:01:40. 000000000 SQL> c/SECOND/MINUTE1 * select numtodsinterval (100, 'minute ') from dualSQL>/NUMTODSINTERVAL (100, 'minute') MINUTE + 000000000 01:40:00. 000000000 SQL> c/MINUTE/HO UR1 * select numtodsinterval (100, 'hour ') from dualSQL>/NUMTODSINTERVAL (100, 'hour') Limit + 000000004 04:00:00. 000000000 SQL> c/HOUR/YEAR1 * select numtodsinterval (100, 'Year') from dualSQL>/select numtodsinterval (100, 'Year') from dual * ERROR at line 1: ORA-01760: illegal argument for function SQL> select numtoyminterval (100, 'Year') f Rom dual; NUMTOYMINTERVAL (100, 'Year') Limit + 000000100-00 SQL> c/YEAR/month1 * select numtoyminterval (100, 'month ') from dualSQL>/NUMTOYMINTERVAL (100, 'month') hour + 000000008-04 time calculation: SQL> select to_date ('2017-12-12 ', 'yyyy-mm-dd ') -to_date ('2017-12-01 ', 'yyyy-mm- Dd') from dual; TO_DATE ('2017-12-12 ', 'yyyy-MM-DD')-TO_DATE ('2017-12-01 ', 'yyyy-MM-DD ') --------------------------------------------------------------------- 11 -- the result of subtraction is a day. SQL> c/1999-12-12/1999-01-121 * select to_date ('2017-01-12 ', 'yyyy-mm-dd')-to_date ('2017-12-01 ', 'yyyy-mm-dd') from dualSQL>/TO_DATE ('2017-01-12 ', 'yyyy-MM-DD')-TO_DATE ('2017-12-01 ', 'yyyy-MM-DD ')----------------------------------- --------------------------------- 323 -- SQL> c/1999-01-12/2999-10-121 * select to_date ('2017-10-12 ', 'yyyy-mm-dd ') -to_date ('2017-12-01 ', 'yyyy-mm-dd') from dualSQL>/TO_DATE ('2017-10-12', 'yyyy-MM-DD ') -TO_DATE ('2017-12-01 ', 'yyyy-MM-DD') --------------------------------------------------------------- 1999 next look at how interval year to month. SQL> create table bb (a date, B date, c interval ye Ar (9) to month); Table created. SQL> desc bb; Name Null? Type types -------- ---------------------------- a dateb datec interval year (9) to month SQL> insert into bb values (to_date ('2017-12-12 ', 'yyyy-mm-dd '), to_date ('2017-12-01 ', 'yyyy-mm-dd'), null) 1 row created. SQL> select * from bb; a B --------- C---------------------------------------------------------------------------12-DEC-85 01-DEC-84 SQL> update bb Set c = numtoyminterval (a-B, 'Year'); 1 row updated. SQL> select * from bb; a B --------- C---------------------------------------------------------------------------12-DEC-85 01-DEC-84 + 000000376-00 -- directly converts the subtracted days to adulthood, Because I specify to change to the year of SQL> select a-B, c from bb; A-B----------C---------------------------------------------------------------------------376 + 000000376-00 SQL> insert into bb values (null, nu Ll, numtoyminterval (376, 'month'); 1 row created. SQL> select * from bb; a B c --------- explain 12-DEC-85 01-DEC-84 + 000000376-00 + 000000031-04 SQL> insert into bb values (null, null, numtoyminterval (999999999, 'Year'); 1 row created. SQL> select * from bb; a B C --------- ----------------------------------------------------------------------- 12-D EC-85 01-DEC-84 + 000000376-00 + 000000031-04 + 999999999-00 ================== INTERVAL YEAR the time difference between the two timestamp types of the to month type. The internal type is 182 and the length is 5. There are 4 bytes of storage year differences, and an offset of 0X80000000 is added during storage. The difference in a byte storage month, which is offset by 60. SQL> alter table TestTimeStamp add e interval year to month; SQL> update testTimeStamp set e = (select interval '5' year + interval '10' month year from dual ); 3 rows have been updated. SQL> commit; submitted completely. SQL> select dump (e, 16) from testTimeStamp; DUMP (E, 16) effectyp = 182 Len = 5: 80, 0, 182, 46Typ = Len = 5: 80, 0, 182, 46Typ = Len = 5: 80, 0, 46: 0X80000005-0X80000000 = May: 0x46-60 = 10 interval day to second Oracle Syntax: INTERVAL '{integer | integer time_expr | time_expr}' {DAY | HOUR | MINUTE} [(leading_precision)] | SECOND [(leading_precision [, fractional_seconds_precision])]} [TO {DAY | HOUR | MINUTE | SECOND [(fractional_seconds_precision)]}] The leading_precision value ranges from 0 TO 9. The default value is 2. the format of time_expr is HH [: MI [: SS [. n] or MI [: SS [. n] or SS [. n], n indicates microseconds. this type is similar to interval year to month. We recommend that you read interval year to month first. value Range: HOUR: 0 to 23 MINUTE: 0 to 59 SECOND: 0 to 59.999999999 eg: INTERVAL '4 5:12:10. 222 'Day to second (3): 4 days, 5 hours, 12 minutes, 10.222 seconds, INTERVAL '4 'day to minute: 4 days, 5 hours, 12 minutes, INTERVAL '2014, 400 5' DAY (3) to hour: 400 days, 5 hours, 400 is 3, so "DAY (3 )", note that the default value is 2. INTERVAL '000000' DAY (3): 400 days INTERVAL '11: 12: 400 'hour to second (7): 11 hours 12 minutes 10.2222222 seconds INTERVAL '11: 20 'hour to minute: 11 hours, 20 minutes, INTERVAL '10' HOUR: 10 hours, INTERVAL '10: 22 'minute to second: 10 minutes 22 seconds INTERVAL '10' MINUTE indicates: 10 minutes INTERVAL '4' DAY indicates: 4 days INTERVAL '25' HOUR indicates: 25 hours INTERVAL '40' MINUTE indicates: 40 minutes INTERVAL '000000' HOUR (3): 120 hours INTERVAL '30. 12345 'second (2, 4) indicates 30.1235 seconds. Because the precision after the SECOND is set to 4, rounding is required. INTERVAL '20' DAY-INTERVAL '000000' HOUR = INTERVAL '10-0' day to second: 20 days-240 hours = 10 days 0 seconds ======================
Interval day to second stores the time difference between two timestamps, expressed in the form of date, hour, minute, and SECOND. The internal code of this data type is 183, and the length is 11 bytes: l four bytes indicate the number of days (increase the offset of 0X80000000) l hours, minutes, And seconds are represented by one byte each (increase the offset of 60) l four bytes indicate the hour difference of the second (increase the offset of 0X80000000) The following is an example: SQL> alter table testTimeStamp add f interval day to second; the table has been changed. SQL> update testTimeStamp set f = (select interval '5' day + interval '10' second from dual); 3 rows have been updated. SQL> commit; submitted completely. SQL> select dump (f, 16) from testTimeStamp; DUMP (F, 16) effectyp = 183 Len = 11: 80, 0, 3c, 3c, 80, 0Typ = 183 Len = 11: 80, 0, 183, 3c, 3c, 0, 0Typ = Len = 11: 80, 0, 3c, 3c, 0, 0 Date: 0X80000005-0X80000000 = 5 hours: 60-60 = 0 minutes: 60-60 = 0 seconds: 70-60 = 10 seconds fractional part: 0X80000000-0X80000000 = 0