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 range of leading_precision values is 0 to 9, and the default is 2. TIME_EXPR in the format: HH[:MI[:SS[.N]]] or MI[:SS[.N]] or SS[.N], N for microseconds.
There are many similarities between this type and interval year to month, and it is recommended to look at interval year to month before looking at the article.
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)
Means: 4 days, 5 hours, 12 minutes, 10.222 seconds
INTERVAL ' 4 5:12 ' Day to MINUTE
Indication: 4 days, 5 hours, 12 minutes
INTERVAL ' 5 ' Day (3) to HOUR
Means: 400 days 5 hours, 400 is 3 for precision, so "day (3)", note the default value is 2.
INTERVAL ' Day (3)
Means: 400 days
INTERVAL ' 11:12:10.2222222 ' HOUR to SECOND (7)
Means: 11 hours, 12 minutes, 10.2222222 seconds
INTERVAL ' 11:20 ' HOUR to MINUTE
Indication: 11 hours 20 minutes
INTERVAL ' HOUR
Means: 10 hours
INTERVAL ' 10:22 ' MINUTE to SECOND
means: 10 minutes 22 seconds
INTERVAL ' MINUTE
Means: 10 points
INTERVAL ' 4 ' Day
Means: 4 days
INTERVAL ' HOUR
Means: 25 hours
INTERVAL ' MINUTE
Means: 40 points
INTERVAL ' HOUR (3)
Means: 120 hours
INTERVAL ' 30.12345 ' SECOND (2,4)
Represents: 30.1235 seconds, because the local second precision is set to 4, to be rounded.
INTERVAL ' day-interval ' HOUR = INTERVAL ' 10-0 ' Day to SECOND
Means: 20 days-240 hours = 10 days 0 seconds
==================
Part of the Source: http://www.oraclefans.cn/forum/showblog.jsp?rootid=140
The INTERVAL day to second type stores the time difference between two timestamp, expressed in date, hour, minute, and second. The internal code for this data type is 183 and the length bit is 11 bytes:
L 4 bytes for number of days (increase 0x80000000 offset)
L hours, minutes, seconds each with one byte (increase 60 offset)
L 4 bytes represents the hour difference of the second (increase 0x80000000 offset)
Here is an example:
Sql> ALTER TABLE TestTimestamp add F interval day to second;
The table has changed.
sql> Update TestTimestamp Set f= (select Interval ' 5 ' Day + interval ' second from dual);
3 rows have been updated.
Sql> commit;
Submit complete.
Sql> Select Dump (f,16) from TestTimestamp;
DUMP (f,16)
--------------------------------------------------------------------------------
typ=183 len=11:80,0,0,5,3c,3c,46,80,0,0,0
typ=183 len=11:80,0,0,5,3c,3c,46,80,0,0,0
typ=183 len=11:80,0,0,5,3c,3c,46,80,0,0,0
Date: 0x80000005-0x80000000=5
Hours: 60-60=0
Minutes: 60-60=0
Seconds: 70-60=10
Seconds Fractional part: 0x80000000-0x80000000=0 "from:http://www.cnblogs.com/snake-hand/archive/2011/02/23/2452308.html"
Oracle INTERVAL Day to second data type
