【SQL】INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND
INTERVAL YEAR TO MONTH: 作为年和月的时间间隔存储
INTERVAL DAY TO SECOND: 作为天、小时、分和秒的时间间隔存储(DAY,HOUR,MINUTE,SECOND)
1) 创建以上两种数据类型的表
SQL> create table t1(id number(2),x interval year to month,y interval day to second);
Table created.
SQL> desc t1;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER(2)
X INTERVAL YEAR(2) TO MONTH
Y INTERVAL DAY(2) TO SECOND(6)
2) x字段插入:5年,y字段插入:2天
SQL> insert into t1 values (1,interval '5' year,interval '2' day);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID X Y
---------- -------------------- --------------------
1 +05-00 +02 00:00:00.000000
3) x字段插入:10个月,y字段插入:2小时
SQL> insert into t1 values (2,interval '10' month,interval '2' hour);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID X Y
---------- -------------------- --------------------
1 +05-00 +02 00:00:00.000000
2 +00-10 +00 02:00:00.000000
4) x字段插入:2年零6个月,y字段插入:1天量12小时30分钟1秒
SQL> insert into t1 values (3,interval '2-6' year to month,interval '1 12:30:01' day to second);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID X Y
---------- -------------------- --------------------
1 +05-00 +02 00:00:00.000000
2 +00-10 +00 02:00:00.000000
3 +02-06 +01 12:30:01.000000