Oracle은 Interval 값을 초로 변환할 때 아래와 같이 다소 복잡한 계산식을 사용해야 합니다.
-- 1
SELECT ts2 - ts1 AS int
, EXTRACT (DAY FROM ts2 - ts1) * 60 * 60 * 24
+ EXTRACT (HOUR FROM ts2 - ts1) * 60 * 60
+ EXTRACT (MINUTE FROM ts2 - ts1) * 60
+ EXTRACT (SECOND FROM ts2 - ts1) AS sec
FROM (SELECT TIMESTAMP '2050-01-01 00:00:00.000000000' AS ts1
, TIMESTAMP '2050-02-01 00:00:00.123456789' AS ts2
FROM DUAL);
INT SEC
----------------------------- -----------------
+000000031 00:00:00.123456789 2678400.123456789
1 row selected.
Interval 값은 산술 연산을 지원하므로 아래의 계산식을 사용하면 비교적 간단한 방법으로 Interval 값을 초로 변환할 수 있습니다. 다만 계산값이 9자리(INTERVAL DAY(9))보다 크면 ORA-01873: the leading precision of the interval is too small 에러가 발생합니다.
-- 2-1
SELECT ts2 - ts1 AS int1
, (ts2 - ts1) * 86400 AS int2
, EXTRACT (DAY FROM (ts2 - ts1) * 24 * 60 * 60) AS sec
FROM (SELECT TIMESTAMP '2050-01-01 00:00:00' AS ts1
, TIMESTAMP '2081-09-09 01:46:39' AS ts2
FROM DUAL);
INT1 INT2 SEC
----------------------------- ----------------------------- ---------
+000011574 01:46:39.000000000 +999999999 00:00:00.000000000 999999999
1 row selected.
-- 2-2
SELECT ts2 - ts1 AS int1
, (ts2 - ts1) * 86400 * 1e3 AS int2
, EXTRACT (DAY FROM (ts2 - ts1) * 24 * 60 * 60 * 1e3) / 1e3 AS sec
FROM (SELECT TIMESTAMP '2050-01-01 00:00:00.000' AS ts1
, TIMESTAMP '2050-01-12 06:54:15.134' AS ts2
FROM DUAL);
INT1 INT2 SEC
----------------------------- ----------------------------- ----------
+000000011 06:54:15.134000000 +975255134 00:00:00.000000000 975255.134
1 row selected.
-- 2-3
SELECT ts2 - ts1 AS int1
, (ts2 - ts1) * 86400 * 1e6 AS int2
, EXTRACT (DAY FROM (ts2 - ts1) * 24 * 60 * 60 * 1e6) / 1e6 AS sec
FROM (SELECT TIMESTAMP '2050-01-01 00:00:00.000000' AS ts1
, TIMESTAMP '2050-01-01 00:16:39.024855' AS ts2
FROM DUAL);
INT1 INT2 SEC
----------------------------- ----------------------------- ----------
+000000000 00:16:39.024855000 +999024855 00:00:00.000000000 999.024855
1 row selected.
-- 2-4
SELECT ts2 - ts1 AS int1
, (ts2 - ts1) * 86400 * 1e6 AS int2
, EXTRACT (DAY FROM (ts2 - ts1) * 24 * 60 * 60 * 1e6) / 1e6 AS sec
FROM (SELECT TIMESTAMP '2050-01-01 00:00:00.000000' AS ts1
, TIMESTAMP '2050-01-01 00:16:39.024856' AS ts2
FROM DUAL);
ORA-01873: the leading precision of the interval is too small
참고