Oracle INTERVAL datatype examples

SQL> select interval '300' month from dual;
INTERVAL'300'MONTH
---------------------------------------------------------------------------
+25-00


1 row selected.


SQL> select interval '1' month from dual;


INTERVAL'1'MONTH
---------------------------------------------------------------------------
+00-01


1 row selected.




SQL> select interval '2-1' year to month from dual;


INTERVAL'2-1'YEARTOMONTH
---------------------------------------------------------------------------
+02-01


1 row selected.




SQL> select interval '02:02:02' hour to second from dual;


INTERVAL'02:02:02'HOURTOSECOND
---------------------------------------------------------------------------
+00 02:02:02.000000


1 row selected.


SQL> select interval '+23 02:02:02'  day to second from dual;


INTERVAL'+2302:02:02'DAYTOSECOND
---------------------------------------------------------------------------
+23 02:02:02.000000


1 row selected.




SQL> select INTERVAL '11:12:10.1234567' HOUR TO SECOND from dual;


INTERVAL'11:12:10.1234567'HOURTOSECOND
---------------------------------------------------------------------------
+00 11:12:10.123457


1 row selected.


SQL> select INTERVAL '11:12:10.1234567' HOUR TO SECOND(2) from dual;


INTERVAL'11:12:10.1234567'HOURTOSECOND(2)
---------------------------------------------------------------------------
+00 11:12:10.12


1 row selected.




SQL> select INTERVAL '10.1234567' SECOND(2,4) from dual
  2  /


INTERVAL'10.1234567'SECOND(2,4)
---------------------------------------------------------------------------
+00 00:00:10.1235


1 row selected.


SQL> select INTERVAL '10.1234567' SECOND(5,4) from dual;


INTERVAL'10.1234567'SECOND(5,4)
---------------------------------------------------------------------------
+00000 00:00:10.1235


1 row selected.


SQL> select INTERVAL '10.1234567' SECOND(1,4) from dual;


INTERVAL'10.1234567'SECOND(1,4)
---------------------------------------------------------------------------
+0 00:00:10.1235


1 row selected.