46.Oracle数据库SQL开发之 日期和时间的存储与处理——使用时间间隔
欢迎转载，转载请标明出处：blog.csdn.net/notbaron/ar…\
9i数据库引入了一种新特性，可以用来存储时间间隔。
1. 使用INTERVAL YEAR TO MONTH类型
INTERVAL YAR TO MONTH类型可以用来存储单位为年和月的时间间隔。
创建表：
CREATE TABLE coupons (
coupon_id INTEGER CONSTRAINT coupons_pk PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
duration INTERVAL YEAR(3) TO MONTH
);
INSERT INTO coupons (
coupon_id, name, duration
) VALUES (
1, '$1 off Z Files', INTERVAL '1' YEAR
);
INSERT INTO coupons (
coupon_id, name, duration
) VALUES (
2, '$2 off Pop 3', INTERVAL '11' MONTH
);
INSERT INTO coupons (
coupon_id, name, duration
) VALUES (
3, '$3 off Modern Science', INTERVAL '14' MONTH
);
INSERT INTO coupons (
coupon_id, name, duration
) VALUES (
4, '$2 off Tank War', INTERVAL '1-3' YEAR TO MONTH
);
INSERT INTO coupons (
coupon_id, name, duration
) VALUES (
5, '$1 off Chemistry', INTERVAL '0-5' YEAR TO MONTH
);
INSERT INTO coupons (
coupon_id, name, duration
) VALUES (
6, '$2 off Creative Yell', INTERVAL '123' YEAR(3)
);
然后执行：
store@PDB1> select * fromcoupons;
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.
2. 使用INTERVAL DAY TO SECOND 类型
INTERVAL DAY TO SECOND类型可以用来存储单位为天和秒的时间间隔。
CREATE TABLE promotions (
promotion_id INTEGER CONSTRAINT promotions_pk PRIMARY KEY,
name VARCHAR2(30) NOT NULL,
duration INTERVAL DAY(3) TO SECOND (4)
);
INSERT INTO promotions (
promotion_id, name, duration
) VALUES (
1,'10% off Z Files', INTERVAL '3' DAY
);
INSERT INTO promotions (
promotion_id, name, duration
) VALUES (
2,'20% off Pop 3', INTERVAL '2' HOUR
);
INSERT INTO promotions (
promotion_id, name, duration
) VALUES (
3,'30% off Modern Science', INTERVAL '25' MINUTE
);
INSERT INTO promotions (
promotion_id, name, duration
) VALUES (
4,'20% off Tank War', INTERVAL '45' SECOND
);
INSERT INTO promotions (
promotion_id, name, duration
) VALUES (
5,'10% off Chemistry', INTERVAL '3 2:25' DAY TO MINUTE
);
INSERT INTO promotions (
promotion_id, name, duration
) VALUES (
6,'20% off Creative Yell', INTERVAL '3 2:25:45' DAY TO SECOND
);
执行如下：
store@PDB1> select * 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.
3. 与时间间隔有关的函数
3.1 NUMTODSINTERVAL()
NUMTODSINTERVAL(x,interval_unit)用于将以interval_unit指定的值为单位的数字x转换为一个INTERVALDAY TO SECOND类型，interval_unit参数可以设置为DAY,HOUR,MINUTE或SECOND。
执行如下：
store@PDB1> select numtodsinterval(1.5,'day'),
numtodsinterval(3.25,'hour'),
numtodsinterval(5,'minute'),
numtodsinterval(10.123456789,'second')
from dual;
NUMTODSINTERVAL(1.5,'DAY')
---------------------------------------------------------------------------
NUMTODSINTERVAL(3.25,'HOUR')
---------------------------------------------------------------------------
NUMTODSINTERVAL(5,'MINUTE')
---------------------------------------------------------------------------
NUMTODSINTERVAL(10.123456789,'SECOND')
---------------------------------------------------------------------------
+000000001 12:00:00.000000000
+000000000 03:15:00.000000000
+000000000 00:05:00.000000000
+000000000 00:00:10.123456789
3.2 NUMTOYMINTERVAL()
NUMTOYMINTERVAL(x,interval_unit)用于将以interval_unit指定的值为单位的数字x转换为一个INTERVALYEAR TO MONTH类型，interval_unit参数可以设置为YEAR或MONTH。
store@PDB1> selectnumtoyminterval(1.5,'year'),
numtoyminterval(3.25,'month') from dual;
NUMTOYMINTERVAL(1.5,'YEAR')
---------------------------------------------------------------------------
NUMTOYMINTERVAL(3.25,'MONTH')
---------------------------------------------------------------------------
+000000001-06
+000000000-03
