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. Internal data typeCodeIt 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