Use date time Interval type: interval day(1) to second(2) : INTERVAL « Date Timezone « Oracle PL / SQL

1. Oracle PL / SQL
2. Date Timezone
3. INTERVAL

Use date time Interval type: interval day(1) to second(2)

 
SQL>
SQL>
SQL> create table employee(
  2        employee_id  number,
  3        break_reason varchar2(100),
  4        break_time   interval day(1) to second(2) );

Table created.

SQL>
SQL>
SQL> insert into employee ( employee_id, break_reason, break_time )
  2       values ( 100, 'COFFEE BREAK',
  3               TIMESTAMP '2001-09-03 12:47:00.000000' -
  4               TIMESTAMP '2001-09-03 13:13:00.000000' );

1 row created.

SQL>
SQL>
SQL> insert into employee ( employee_id, break_reason, break_time )
  2        values ( 100, 'BIO BREAK',
  3               TIMESTAMP '2001-09-03 13:35:00.000000' -
  4               TIMESTAMP '2001-09-03 13:39:00.000000' );

1 row created.

SQL>
SQL> insert into employee ( employee_id, break_reason, break_time )
  2        values ( 100, 'PUB BREAK',
  3               TIMESTAMP '2001-09-03 16:30:00.000000' -
  4               TIMESTAMP '2001-09-03 17:00:00.000000' );

1 row created.

SQL>
SQL> insert into employee ( employee_id, break_reason, break_time )
  2        values ( 100, 'FOOTBALL SCORE UPDATE',
  3               TIMESTAMP '2001-09-03 17:00:00.000000' -
  4               TIMESTAMP '2001-09-03 17:30:00.000000' );

1 row created.

SQL>
SQL> column break_reason format a30
SQL> column break_time format a30
SQL>
SQL>
SQL> select employee_id, break_reason, break_time from employee;

EMPLOYEE_ID BREAK_REASON                   BREAK_TIME
----------- ------------------------------ ------------------------------
        100 COFFEE BREAK                   -0 00:26:00.00
        100 BIO BREAK                      -0 00:04:00.00
        100 PUB BREAK                      -0 00:30:00.00
        100 FOOTBALL SCORE UPDATE          -0 00:30:00.00

4 rows selected.

SQL>
SQL> drop table employee;

Table dropped.

SQL>

 

Related examples in the same category