INTERVAL DAY TO SECOND 型と INTERVAL YEAR TO MONTH 型

しらなかった。

INTERVAL DAY TO SECOND はこんな感じ。

SQL> desc test1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 DAY1                                               DATE
 INT                                                INTERVAL DAY(2) TO SECOND(6)

SQL> select day1, int, day1 - int from test1;

DAY1                INT                            DAY1-INT
------------------- ------------------------------ -------------------
2021-04-06 19:10:44 +01 00:00:00.000000            2021-04-05 19:10:44
2021-04-06 19:10:44 +01 01:02:03.000000            2021-04-05 18:08:41

INTERVAL YEAR TO MONTH はこんな感じ。

SQL> desc test2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 DAY1                                               DATE
 INT                                                INTERVAL YEAR(2) TO MONTH

SQL> select day1, int, day1 - int from test2;

DAY1                INT                            DAY1-INT
------------------- ------------------------------ -------------------
2021-04-06 19:10:55 +01-00                         2020-04-06 19:10:55
2021-04-06 19:10:55 +01-03                         2020-01-06 19:10:55

つかったSQLは以下

create table test1 (id number, day1 date, int INTERVAL DAY TO SECOND) ;
insert into test1 values (1,sysdate,NUMTODSINTERVAL(1, 'DAY') );
insert into test1 values (2,sysdate,TO_DSINTERVAL('1 01:02:03'));
commit;

create table test2 (id number, day1 date, int INTERVAL year TO month) ;
insert into test2 values (1,sysdate,NUMTOYMINTERVAL(1, 'YEAR') );
insert into test2 values (2,sysdate,TO_YMINTERVAL('01-03'));
commit;


select day1, int, day1 - int from test1;
select day1, int, day1 - int from test2;