Shulou(Shulou.com)05/31 Report--

This article shows you how to understand oracle's interval time format, which is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

INTERVAL: interval

INTERVAL YEAR TO MONTH, between year and month there must be-

The interval between a year and a month.

INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)] [TO {YEAR | MONTH}]

'integer [- integer] 'specifies integer values for the leading and optional trailing field of the literal. If the leading field is YEAR and the trailing field is MONTH, then the range of integer values for the month field is 0 to 11.

Precision is the maximum number of digits in the leading field. The valid range of the leading field precision is 0 to 9 and its default value is 2.

Restriction on the Leading Field: If you specify a trailing field, then it must be less significant than the leading field. For example, INTERVAL'0-1 'MONTH TO YEAR is not valid.

"integer [- integer]" specifies the integer values for leading and optional trailing fields. If the leading field is a year and the trailing field is a month, the integer values for the month field range from 0 to 11.

"precision" specifies the maximum number of digits for the leading field. The valid range of leading field precision is 0 to 9, with a default value of 2.

Leading column restrictions: if you specify a trailing field, it must be smaller than the leading field. For example, INTERVAL'0-1 'months to YEAR is invalid.

Select sysdate,sysdate + interval'1' year from dual;-- current time + 1 year

Select sysdate,sysdate+numtoyminterval (1 minute yearbook) as res from dual;-- current time + 1 year

Select sysdate,sysdate + interval'1-0' year to month from dual;-- current time + 1 year

Select sysdate,sysdate + interval'1-1 'year to month from dual;-- current time + January of 1 year

Select sysdate,sysdate + interval '100' year (3) from dual;-- current time + 100 years

Select sysdate,sysdate + interval '100-1' year (3) to month from dual;-- current time + January 100

Select sysdate,sysdate + interval '100' year (2) from dual;-- error ORA-01873: the leading precision of the interval is too small because 100is a 3-digit number, while 2 only specifies two digits

Select sysdate,sysdate + interval '100-1' year (2) to month from dual;-- error ORA-01873: the leading precision of the interval is too small because 100is a 3-digit number, while 2 only specifies two digits

INTERVAL DAY TO SECOND

The interval of days, hours, minutes, and seconds.

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

'integer' specifies the number of days. If this value contains more digits than the number specified by the leading precision, then Oracle returns an error.

'time_expr' specifies a time in the format HH [: MI [: SS [.n]] or MI [: SS [.n]] or SS [.n], where n specifies the fractional part of a second. If n contains more digits than the number specified by fractional_seconds_precision, then n is rounded to the number of digits specified by the fractional_seconds_precision value. You can specify time_expr following an integer and a space only if the leading field is DAY.

"integer" specifies the number of days. If this value contains a number that exceeds the number specified by the primary precision, Oracle returns an error.

"time_expr" specifies the time of the format HH:MI:SS.

Select sysdate,sysdate+1 from dual;-- current time + 1 day

Select sysdate,sysdate + interval'1' day from dual;-- current time + 1 day

Select sysdate,sysdate+numtodsinterval (1) as res from dual;-- current time + 1 day

Select sysdate,sysdate + interval '10' day to hour from dual;-- current time + 1 day

Select sysdate,sysdate + interval '11' day to hour from dual;-- current time + 1 day and 1 hour

Select sysdate,sysdate + interval'1 01day to minute from dual;-- current time + 1 day, 1 hour and 1 minute

Select sysdate,sysdate + interval'1 01 day to second from dual;-- current time + 1 day 1 hour 1 minute 1 second

Select sysdate,sysdate-interval'1' year,sysdate-interval'1' month,sysdate-interval'1' day,sysdate-interval'1' HOUR,sysdate-interval'1' MINUTE,sysdate-interval'1' second from dual

-- at this moment, 1 year ago, 1 month ago, 1 day ago, 1 hour ago, 1 minute, 1 second ago

The above content is how to understand the interval time format of oracle. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.