[MySQL/Oracle] TIMESTAMP 타입에 대한 소요 시간(정수) 계산 - 이슈트래킹

---

실무 사례

운영 중인 시스템에서 배치 작업 실행 로그를 분석하다가, 시작 시간(start_time)과 종료 시간(end_time)을 이용해 총 소요 시간(초 단위)을 계산해야 하는 일이 있었다.

MySQL의 경우 TIMESTAMPDIFF를 사용하여 간단하게 해결하였으나, 오라클의 경우 해당 함수 지원이 되지 않아 단순 숫자 연산으로 처리하였는데, 이 경우 날짜 차이 계산이 정확하지 않은 이슈가 있었다.

 

---

예시로 다음과 같은 테이블과 데이터를 준비한다.

-- 샘플 테이블
CREATE TABLE JOB_EXECUTION_LOG (
    job_id       INT,
    start_time   TIMESTAMP,
    end_time     TIMESTAMP
);

INSERT INTO JOB_EXECUTION_LOG VALUES
(1, '2025-08-15 10:00:00', '2025-08-15 10:05:30'),
(2, '2025-08-15 23:59:50', '2025-08-16 00:00:10');

 

MYSQL

MySQL  TIMESTAMPDIFF 사용법

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

 

unit 값	설명	예시
SECOND	초 차이	330
MINUTE	분 차이	5
HOUR	시간 차이	1
DAY	일 차이	2

 

MySQL  TIMESTAMPDIFF 적용 쿼리

SELECT TIMESTAMPDIFF(SECOND, start_time, end_time) AS elapsed_seconds
FROM JOB_EXECUTION_LOG
LIMIT 1;

 

---

오라클

오라클의 문제 사례

SELECT TO_NUMBER(TO_NUMBER(TO_CHAR(end_time, 'YYYYMMDDHH24MISS'))
       - TO_NUMBER(TO_CHAR(start_time, 'YYYYMMDDHH24MISS')))
FROM JOB_EXECUTION_LOG
WHERE ROWNUM <= 1;

 

• TO_CHAR로 문자열 변환 후 단순 숫자 차를 계산
• 20250815235950 - 20250816000010 같이 날짜 형식 숫자를 빼면 일자·월·년 변화가 반영되지 않고 단순 자리수 차이만 계산됨
• 하루를 넘어가는 경우, 음수 또는 엉뚱한 값이 나옴

DATE로 CAST해야 하는 이유 (Oracle)

• Oracle의 TIMESTAMP는 날짜 + 나노초 단위까지 저장됨
• TIMESTAMP끼리 뺄셈을 바로 하면 INTERVAL 타입이 반환되는데, 이를 숫자로 바로 연산하면 복잡해짐
• DATE 타입으로 캐스팅하면 소수 형태의 일(day) 값으로 단순화되어, 일/시/분/초 단위 계산이 쉬워짐
• 특히 배치 실행 시간 계산처럼 초 단위 정확도가 필요한 경우, (DATE 차이) × 86400 방식이 가장 간단하고 안정적임

오라클 올바른 방식

SELECT (CAST(end_time AS DATE) - CAST(start_time AS DATE)) * 86400 AS elapsed_seconds
FROM JOB_EXECUTION_LOG
WHERE ROWNUM <= 1;

 

 

• CAST(... AS DATE) : TIMESTAMP → DATE 변환 (시간까지 포함됨)
• Oracle에서 DATE 타입끼리 뺄셈 시, 결과는 일(day) 단위의 소수로 반환됨
• 1일 = 24시간 × 60분 × 60초 = 86400초 이므로, 이를 곱해 초 단위로 변환

예시 결과

job_id	start_time	end_time	elapsed_seconds
1	2025-08-15 10:00:00	2025-08-15 10:05:30	330
2	2025-08-15 23:59:50	2025-08-16 00:00:10	20