NTERVALYEARTOMONTH Oracle Syntax: INTERVALinteger [-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: INTER
Nterval year to month 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: INTER
Nterval 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 exact range of year and month. precision is the exact range of year or month. The valid range is 0 to 9, and the default value is 2.
Eg:
INTERVAL '1970-2' YEAR (3) TO MONTH
"YEAR (3)" indicates that the accuracy of the YEAR is 3. It can be seen that "123" is just 3 as a valid value. If the YEAR (n ), if n is less than 3, an error occurs. Note that the default value is 2.
INTERVAL '000000' YEAR (3)
Indicates 0 months from January 1, 123.
INTERVAL '000000' MONTH (3)
300 months. Note that the precision of the MONTH is 3.
INTERVAL '4' YEAR
Indicates four years, which is the same as INTERVAL '4-0' year to month.
INTERVAL '50' MONTH
Indicates 50 months, which is the same as INTERVAL '4-2' year to month.
INTERVAL '000000' YEAR
This field indicates that there is an error. The accuracy of 123 is 3, but the default value is 2. Therefore, INTERVAL '000000' YEAR (3) should be written here) or "3" to a value greater than 3 and less than or equal to 9.
INTERVAL '5-3 'year to month + INTERVAL '20' MONTH =
INTERVAL '6-11' YEAR TO MONTH
Indicates 5 years, 3 months, and 20 months = 6 years, 11 months
Functions related to this type:
NUMTODSINTERVAL (n, 'interval _ unit ')
Converts n to the value specified by interval_unit. interval_unit can be: DAY, HOUR, MINUTE, 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/SECOND
1 * select numtodsinterval (100, 'second') from dual
SQL>/
NUMTODSINTERVAL (100, 'second ')
---------------------------------------------------------------------------
+ 000000000 00:01:40. 000000000
SQL> c/SECOND/MINUTE
1 * select numtodsinterval (100, 'minute ') from dual
SQL>/
NUMTODSINTERVAL (100, 'minute ')
---------------------------------------------------------------------------
+ 000000000 01:40:00. 000000000
SQL> c/MINUTE/HOUR
1 * select numtodsinterval (100, 'hour') from dual
SQL>/
NUMTODSINTERVAL (100, 'hour ')
---------------------------------------------------------------------------
+ 000000004 04:00:00. 000000000
SQL> c/HOUR/YEAR
1 * select numtodsinterval (100, 'Year') from dual
SQL>/
Select numtodsinterval (100, 'Year') from dual
*
ERROR at line 1:
ORA-01760: illegal argument for function
SQL> select numtoyminterval (100, 'Year') from dual;
NUMTOYMINTERVAL (100, 'Year ')
---------------------------------------------------------------------------
+ 000000100-00
SQL> c/year/month
1 * select numtoyminterval (100, 'month') from dual
SQL>/
NUMTOYMINTERVAL (100, 'month ')
---------------------------------------------------------------------------
+ 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-12
1 * select to_date ('2017-01-12 ', 'yyyy-mm-dd')-to_date ('2017-12-01', 'yyyy-mm-dd') from dual
SQL>/
TO_DATE ('2017-01-12 ', 'yyyy-MM-DD')-TO_DATE ('2017-12-01 ', 'yyyy-MM-DD ')
---------------------------------------------------------------------
-323
-- It can also be a negative number.
SQL> c/1999-01-12/2999-10-12
1 * select to_date ('2017-10-12 ', 'yyyy-mm-dd')-to_date ('2017-12-01', 'yyyy-mm-dd') from dual
SQL>/
TO_DATE ('2017-10-12 ', 'yyyy-MM-DD')-TO_DATE ('2017-12-01 ', 'yyyy-MM-DD ')
---------------------------------------------------------------------
365193
Next let's take a look at how TO use interval year to month.
SQL> create table bb (a date, B date, c interval year (9) to month );
Table created.
SQL> desc bb;
Name Null? Type
-----------------------------------------------------------------------------
A DATE
B DATE
C interval year (9) TO MONTH
SQL> insert into bb values (to_date ('1970-12-12 ', 'yyyy-mm-dd'), to_date ('1970-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 change the subtracted days to an adult, Because I specify to change the days to years.
SQL> select a-B, c from bb;
A-B
----------
C
---------------------------------------------------------------------------
376
+ 000000376-00
SQL> insert into bb values (null, null, numtoyminterval (376, 'month '));
1 row created.
SQL> select * from bb;
A B C
--------------------------------------------------------------
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-DEC-85 01-DEC-84 + 000000376-00
+ 000000031-04
+ 999999999-00
======================================
The time difference between the two TIMESTAMP types of interval year to month. 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.
SQL> select dump (e, 16) from testTimeStamp;
DUMP (E, 16)
---------------------------------------------
Typ = 182 Len = 5: 80, 0, 46
Typ = 182 Len = 5: 80, 0, 46
Typ = 182 Len = 5: 80, 0, 46
Year: 0X80000005-0X80000000 = 5
Month: 0x46-60 = 10
Interval day to second data type
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.
Range value:
HOUR: 0 to 23
MINUTE: 0 to 59
SECOND: 0 to 59.999999999
Eg:
INTERVAL '4 5:12:10. 222 'Day to second (3)
Indicates 4 days, 5 hours, 12 minutes, and 10.222 seconds.
INTERVAL '4:12 'DAY TO MINUTE
Indicates 4 days, 5 hours, and 12 minutes.
INTERVAL '0000005' DAY (3) TO HOUR
The value is 5 hours in 400 days, and 3 in 400 is the precision. Therefore, "DAY (3)". Note that the default value is 2.
INTERVAL '000000' DAY (3)
400 days
INTERVAL '11: 12: 10.2222222 'hour to second (7)
Indicates 11 hours, 12 minutes, 10.2222222 seconds.
INTERVAL '11: 20' HOUR TO MINUTE
Indicates 11 hours and 20 minutes.
INTERVAL '10' HOUR
10 hours
INTERVAL '10: 22' MINUTE TO SECOND
Indicates 10 minutes and 22 seconds.
INTERVAL '10' MINUTE
Meaning: 10 points
INTERVAL '4' DAY
Indicates four days.
INTERVAL '25' HOUR
Representation: 25 hours
INTERVAL '40' MINUTE
Indicates: 40 points
INTERVAL '000000' HOUR (3)
120 hours
INTERVAL '30. 100' SECOND (12345)
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
Indicates 20 days-240 hours = 10 days, 0 seconds
============================
This part Source: http://www.Oraclefans.cn/forum/showblog.jsp? Rootid = 140
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 The hour, minute, and second are represented by one byte (increase by 60 offset)
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.
SQL> select dump (f, 16) from testTimeStamp;
DUMP (F, 16)
--------------------------------------------------------------------------------
Typ = 183 Len = 11: 80, 0, 3c, 3c, 0, 0
Typ = 183 Len = 11: 80, 0, 3c, 3c, 0, 0
Typ = 183 Len = 11: 80, 0, 3c, 3c, 0, 0
Date: 0X80000005-0X80000000 = 5
Hour: 60-60 = 0
Minute: 60-60 = 0
Seconds: 70-60 = 10
Second decimal part: 0X80000000-0X80000000 = 0
