Oracle PL/SQL - PL SQL Data Type INTERVAL Type

Introduction

INTERVAL Type represents time duration.

Demo

SQL>
SQL> declare-- from   w w w .jav a  2 s . co  m
  2       v_start_ts  TIMESTAMP:= to_timestamp('14:00:00','HH24:MI:SS');
  3       v_end_ts    TIMESTAMP:= to_timestamp('15:12:24','HH24:MI:SS');
  4       v_delta_int INTERVAL DAY TO SECOND;
  5  begin
  6      v_delta_int:=v_end_ts-v_start_ts;
  7      DBMS_OUTPUT.put_line(v_delta_int);
  8  end;
  9  /
+00 01:12:24.000000

PL/SQL procedure successfully completed.
SQL>

INTERVAL includes two data types that allow you to set appropriate durations more precisely:

declare
     variable1_int INTERVAL YEAR[(precision)] TO MONTH;
     variable2_int INTERVAL DAY[(precision)] to
              SECOND[(precision)];
...

INTERVAL YEAR TO MONTH data type allows you to store and manipulate intervals of years and months.

You can specify the number of digits in years that you want to store. By default 2, available range is 0-4.

INTERVAL DAY TO SECOND data type allows you to store and manipulate intervals of days, hours, minutes, and seconds.

Day precision allows you to set the number of digits, and second precision identifies the number of digits used to store fractions of seconds.

• Previous
• Next