Interval 값을 초로 변환

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

 

참고

• Stack Overflow - Extracting the total number of seconds from an interval data-type