Oralce 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 range of leading_precision values is 0 to 9, and the default is 2. The time_expr format is: HH[:MI[:SS[.N]] or MI[:SS[.N]] or SS[.N, and N represents a microsecond.

There are many similarities between this type and interval year to month, and it is recommended that you look at interval 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)

said: 4 days 5 hours 12 minutes 10.222 seconds

INTERVAL ' 4 5:12 ' to MINUTE

said: 4 days 5 hours 12 minutes

INTERVAL ' 5 ' Day (3) to HOUR

Indicates: 400 days 5 hours, 400 is 3 precision, so "day (3)", note that the default value is 2.

INTERVAL ' Day (3)

said: 400 days

INTERVAL ' 11:12:10.2222222 ' HOUR to SECOND (7)

said: 11 hours 12 minutes 10.2222222 seconds

INTERVAL ' 11:20 ' HOUR to MINUTE

said: 11 hours 20 minutes

INTERVAL ' HOUR '

said: 10 hours

INTERVAL ' 10:22 ' MINUTE to SECOND

said: 10 minutes 22 seconds

INTERVAL ' MINUTE '

said: 10 points

INTERVAL ' 4 ' Day

said: 4 days

INTERVAL ' HOUR '

said: 25 Hours

INTERVAL ' MINUTE '

said: 40 points

INTERVAL ' HOUR ' (3)

said: 120 hours

INTERVAL ' 30.12345 ' SECOND (2,4)

Represents: 30.1235 seconds, because the precision of the local seconds is set to 4, to be rounded.

INTERVAL ' day-interval ' HOUR = INTERVAL ' 10-0 ' Day to SECOND

said: 20 days-240 hours = 10 days 0 seconds

==================

This article URL address: http://www.bianceng.cn/database/Oracle/201410/45389.htm

The INTERVAL day to second type stores the time difference between two timestamp, expressed as a date, hour, minute, and second. The internal code for this data type is 183, the length bit 11 bytes:

L 4 bytes represents days (increase 0x80000000 offset)

L hours, minutes, and seconds are expressed in one byte (increase by 60 offset)

L 4 bytes representing 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 completed.

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

Min: 60-60=0

Seconds: 70-60=10

Seconds Decimal part: 0x80000000-0x80000000=0