Oracle PL/SQL/Date Timezone/INTERVAL — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
INTERVAL DAY(3) TO SECOND (4)
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>
INTERVAL DAY TO SECOND
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>
INTERVAL DAY TO SECOND type to store time intervals measured in days and seconds
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.
INTERVAL YEAR(3) TO MONTH
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;
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>
Using the INTERVAL YEAR TO MONTH Type
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;