Oracle PL/SQL/Date Timezone/INTERVAL — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 10:01, 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;
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Date_Timezone/INTERVAL&oldid=2453»