Oracle PL/SQL/Date Timezone/INTERVAL
Содержание
INTERVAL DAY(3) TO SECOND (4)
<source lang="sql">
SQL> CREATE TABLE promotions (
2 promotion_id INTEGER, 3 name VARCHAR2(30), 4 duration INTERVAL DAY(3) TO SECOND (4) 5 );
Table created. SQL> SQL> SQL> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (1, "10% off Z Files", INTERVAL "3" DAY);
1 row created. SQL> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (2, "20% off Pop 3", INTERVAL "2" HOUR);
1 row created. SQL> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (3, "30% off Modern Science", INTERVAL "25" MINUTE);
1 row created. SQL> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (4, "20% off Tank War", INTERVAL "45" SECOND);
1 row created. SQL> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (5, "10% off Chemistry", INTERVAL "3 2:25" DAY TO MINUTE);
1 row created. SQL> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (6, "20% off Creative Yell", 3 INTERVAL "3 2:25:45" DAY TO SECOND);
1 row created. SQL> INSERT INTO promotions (promotion_id, name, duration)
2 VALUES (7, "15% off My Front Line", 3 INTERVAL "123 2:25:45.12" DAY(3) TO SECOND(2));
1 row created. SQL> SQL> SELECT *
2 FROM promotions;
PROMOTION_ID NAME DURATION
------------------------------ ---------------------------------------------------------------------------
1 10% off Z Files +003 00:00:00.0000
2 20% off Pop 3 +000 02:00:00.0000
3 30% off Modern Science +000 00:25:00.0000
4 20% off Tank War +000 00:00:45.0000
5 10% off Chemistry +003 02:25:00.0000
6 20% off Creative Yell +003 02:25:45.0000
7 15% off My Front Line +123 02:25:45.1200
7 rows selected. SQL> SQL> drop table promotions; Table dropped. SQL> SQL>
</source>
INTERVAL DAY TO SECOND
<source lang="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> insert into employee ( employee_id, break_reason, break_time )
2 values ( 100, "C", 3 TIMESTAMP "2001-09-03 12:47:00.000000" - 4 TIMESTAMP "2001-09-03 13:13:00.000000" );
1 row created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 100, "B", 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, "P", 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, "F", 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> select employee_id, break_reason, break_time from employee; EMPLOYEE_ID BREAK_REASON BREAK_TIME
------------------------------ ------------------------------
100 C -0 00:26:00.00
100 B -0 00:04:00.00
100 P -0 00:30:00.00
100 F -0 00:30:00.00
SQL> SQL> drop table employee; Table dropped. SQL>
</source>
INTERVAL DAY TO SECOND type to store time intervals measured in days and seconds
<source lang="sql">
SQL> -- INTERVAL DAY TO SECOND type to store time intervals measured in days and seconds. SQL> SQL> CREATE TABLE promotions (
2 promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY, 3 name VARCHAR2(30) NOT NULL, 4 duration INTERVAL DAY(3) TO SECOND (4) 5 );
Table created. SQL> SQL> SQL> desc promotions;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- ----------------------- PROMOTION_ID NOT NULL NUMBER(38) NAME NOT NULL VARCHAR2(30) DURATION INTERVAL DAY(3) TO SECOND(4)
SQL> SQL> drop table promotions; Table dropped.
</source>
INTERVAL YEAR(3) TO MONTH
<source lang="sql">
SQL> CREATE TABLE coupons (
2 coupon_id INTEGER, 3 name VARCHAR2(30), 4 duration INTERVAL YEAR(3) TO MONTH 5 );
Table created. SQL> SQL> SQL> INSERT INTO coupons (coupon_id, name, duration) VALUES (1, "$1 off Z Files", INTERVAL "1" YEAR); 1 row created. SQL> INSERT INTO coupons (coupon_id, name, duration) VALUES (2, "$2 off Pop 3", INTERVAL "11" MONTH); 1 row created. SQL> INSERT INTO coupons (coupon_id, name, duration) VALUES (3, "$3 off Modern Science", INTERVAL "14" MONTH); 1 row created. SQL> INSERT INTO coupons (coupon_id, name, duration) VALUES (4, "$2 off Tank War", INTERVAL "1-3" YEAR TO MONTH); 1 row created. SQL> INSERT INTO coupons (coupon_id, name, duration) VALUES (5, "$1 off Chemistry", INTERVAL "0-5" YEAR TO MONTH); 1 row created. SQL> INSERT INTO coupons (coupon_id, name, duration) VALUES (6, "$2 off Creative Yell", INTERVAL "123" YEAR(3)); 1 row created. SQL> SQL> SELECT * FROM coupons;
COUPON_ID NAME DURATION
------------------------------ ---------------------------------------------------------------------------
1 $1 off Z Files +001-00
2 $2 off Pop 3 +000-11
3 $3 off Modern Science +001-02
4 $2 off Tank War +001-03
5 $1 off Chemistry +000-05
6 $2 off Creative Yell +123-00
6 rows selected. SQL> SQL> SQL> SQL> drop table coupons;
</source>
Use date time Interval type: interval day(1) to second(2)
<source lang="sql">
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>
</source>
Using the INTERVAL YEAR TO MONTH Type
<source lang="sql">
SQL> SQL> -- Using the INTERVAL YEAR TO MONTH Type SQL> SQL> -- INTERVAL YEAR TO MONTH type to store time intervals measured in years and months. SQL> SQL> CREATE TABLE coupons (
2 coupon_id INTEGER, 3 name VARCHAR2(30), 4 duration INTERVAL YEAR(3) TO MONTH 5 );
Table created. SQL> SQL> desc coupons;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------- COUPON_ID NUMBER(38) NAME VARCHAR2(30) DURATION INTERVAL YEAR(3) TO MONTH
SQL> SQL> drop table coupons;
</source>
