Use TIMESTAMP in insert statement to add value to interval type column : Time Interval « SQL Data Types « Oracle PL/SQL Tutorial






SQL> create table myTable(
  2        break_time   interval day(1) to second(2)
  3  );

Table created.

SQL>
SQL>
SQL> insert into myTable (break_time )
  2  values (TIMESTAMP '2001-09-03 12:47:00.000000' - TIMESTAMP '2001-09-03 13:13:00.000000' );

1 row created.

SQL>
SQL> insert into myTable ( break_time )
  2  values ( TIMESTAMP '2001-09-03 13:35:00.000000' - TIMESTAMP '2001-09-03 13:39:00.000000' );

1 row created.

SQL>
SQL> insert into myTable ( break_time )
  2  values (TIMESTAMP '2001-09-03 16:30:00.000000' - TIMESTAMP '2001-09-03 17:00:00.000000' );

1 row created.

SQL>
SQL> insert into myTable ( break_time )
  2  values ( TIMESTAMP '2001-09-03 17:00:00.000000' - TIMESTAMP '2001-09-03 17:30:00.000000' );

1 row created.

SQL>
SQL> column break_time format a30
SQL>
SQL> select * from myTable;


BREAK_TIME
------------------------------
-0 00:26:00.00
-0 00:04:00.00
-0 00:30:00.00
-0 00:30:00.00

4 rows selected.

SQL>
SQL> drop table myTable;

Table dropped.








10.10.Time Interval
10.10.1.Using Time Intervals
10.10.2.interval type column
10.10.3.Format interval type column
10.10.4.Insert value to interval type column
10.10.5.Use TIMESTAMP in insert statement to add value to interval type column
10.10.6.INTERVAL '1' YEAR: Interval of 1 year
10.10.7.INTERVAL '11' MONTH: Interval of 11 months
10.10.8.INTERVAL '14' MONTH: Interval of 14 months (equivalent to 1 year 2 months)
10.10.9.INTERVAL '1-3' YEAR TO MONTH: Interval of 1 year 3 months
10.10.10.INTERVAL '0-5' YEAR TO MONTH: Interval of 0 years 5 months
10.10.11.INTERVAL '123' YEAR(3) TO MONTH: Interval of 123 years with a precision of 3 digits
10.10.12.INTERVAL '-1-5' YEAR TO MONTH: A negative interval of 1 year 5 months
10.10.13.INTERVAL '1234' YEAR(3):
10.10.14.Using the INTERVAL DAY TO SECOND Type
10.10.15.INTERVAL '3' DAY: Interval of 3 days
10.10.16.INTERVAL '2' HOUR: Interval of 2 hours
10.10.17.INTERVAL '25' MINUTE: Interval of 25 minutes
10.10.18.INTERVAL '45' SECOND: Interval of 45 seconds
10.10.19.INTERVAL '3 2:25:45' DAY TO SECOND: Interval of 3 days 2 hours 25 minutes 45 seconds
10.10.20.INTERVAL '123 2:25:45.12' DAY(3) TO SECOND(2): Interval of 123 days 2 hours 25 minutes 45.12 seconds; the precision for days is 3 digits and the precision for the fractional seconds is 2 digits
10.10.21.INTERVAL '3 2:00:45' DAY TO SECOND: Interval of 3 days 2 hours 0 minutes 45 seconds
10.10.22.INTERVAL '-3 2:25:45' DAY TO SECOND: Negative interval of 3 days 2 hours 25 minutes 45 seconds
10.10.23.INTERVAL '1234 2:25:45' DAY(3) TO SECOND: Invalid interval because the number of digits in the days exceeds the specified precision of 3
10.10.24.INTERVAL '123 2:25:45.123' DAY TO SECOND(2): Invalid interval because the number of digits in the fractional seconds exceeds the specified precision of 2