Plsql_ Basic Series 4_ time interval interval

2014-12-08 Baoxinjian

I. Summary

The interval data type is used to store a time interval between two timestamps.

You can specify an interval between years and months, or days,hours,minuts,seconds.

Oracle supports two types of inteval, each of which is year-to-month and day-to-SECOND.

Each type contains leading field and trailing field. The main parameter defines the date or time to be computed, and the secondary parameter defines the minimum growth amount

Second, the grammar

1. 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)]}]

(1). The range of leading_precision values is 0 to 9, which is 2 by default. TIME_EXPR in the format: HH[:MI[:SS[.N]]] or MI[:SS[.N]] or SS[.N], N for microseconds.

(2). There are many similarities between this type and interval year to month, it is recommended to look at interval year to month before looking at the article.

2. Range values:

Hour:0 to 23

Minute:0 to 59

second:0 to 59.999999999

Third, case analysis

1. 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

2. INTERVAL ' 30.12345 ' SECOND (2,4)

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

3. Other

Select Date ' 2010-01-01 ' + interval ' + ' year from dual

Select Date ' 2010-01-01 ' + interval ' 123-2 ' year (4) to month from dual

Select Date ' 2010-01-01 ' + interval ' + ' month from dual

Select Date ' 2010-01-01 ' + Interval ' Day (2) from dual

Select Date ' 2010-01-01 ' + interval ' 1 2 ' day to hour from dual

Select Date ' 2010-01-01 ' + interval ' 1 2:3 ' day to minute from dual

Select Date ' 2010-01-01 ' + interval ' 1 2:3:4 ' day to second from dual

Select Date ' 2010-01-01 ' + interval ' 1 ' hour from dual

Select Date ' 2010-01-01 ' + interval ' 1:2 ' hour to minute from dual

Select Date ' 2010-01-01 ' + interval ' 1:2:3 ' hour to second from dual

Select Date ' 2010-01-01 ' + interval ' 1:2 ' minute to second from dual

Thanks and regards

Plsql_ Basic Series 4_ time interval interval