ORACLE 날짜 및 시간 데이터 유형

ORACLE 날짜 및 시간 데이터 유형에 대해 알아보겠습니다. 날짜 및 시간 데이터 유형에는 DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE이 있습니다. 유형별 특징을 알아볼까요?

DATE

날짜 및 시간 정보를 저장하는 데 사용되는 데이터 유형으로 년, 월, 일, 시, 분, 초 정보까지 포함하고 있습니다. ORACLE에서 가장 일반적으로 많이 사용되는 날짜 유형이기도 합니다. 기본형식은 'YYYY-MM-DD' 형식으로 저장이 되며 시, 분, 초는 '00 시 00분 00초'로 자동 저장됩니다. DATE 타입은 초까지 저장을 할 수 있지만 밀리 초까지 표현을 할 순 없습니다. 만약 밀리 초까지 표시를 해야 한다면 TIMESTAMP를 사용해야 합니다. DATE는 ORACLE에서 제공하는 TO_CHAR, TO_DATE를 통해 문자타입이나 날짜타입으로 형태를 변환할 수 있습니다.

-- DUAL은 가상의 테이블입니다. 단일 행과 단일열을 가지며 하나의 레코드만 출력할때 사용합니다.
-- 현재 날짜와 시간을 가져옵니다.
SELECT SYSDATE FROM DUAL;   -- 2023-04-17 19:10:27

--특정날짜와 시분초까지 지정해서 DATE형식으로 만들 수 있습니다.
--HH24는 24시간 기준, HH는 12시간 기준 AM/PM을 별도로 표시합니다.
SELECT TO_DATE('2023-04-17 19:10:27', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- 2023-04-17 19:10:27
SELECT TO_DATE('2023-04-17 19:10:27', 'YYYY-MM-DD HH:MI:SS') FROM DUAL; -- 2023-04-17 PM 07:10:27

-- 입력한날짜의 형식과 동일한 포멧팅형식으로 지정해야합니다.
SELECT TO_DATE('20230417191027', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- 오류발생
SELECT TO_DATE('20230417191027', 'YYYYMMDDHH24MISS') FROM DUAL; -- 2023-04-17 19:10:27

-- DATE에서 년, 월, 일, 시, 분, 초를 추출하는 예제입니다.
-- EXTRACT는 DATE나 TIMESTAMP에서 특정부분을 추출하는 함수이고 ORACLE 버전9i부터 사용할 수 있습니다.
SELECT EXTRACT(YEAR FROM SYSDATE) AS "년"     -- 2023
     , EXTRACT(MONTH FROM SYSDATE) AS "월"    -- 4
     , EXTRACT(DAY FROM SYSDATE) AS "일"      -- 17
     , EXTRACT(HOUR FROM SYSDATE) AS "시"     -- 19
     , EXTRACT(MINUTE FROM SYSDATE) AS "분"   -- 10
     , EXTRACT(SECOND FROM SYSDATE) AS "초"   -- 27
  FROM DUAL;
  
-- DATE에서 년, 월, 일, 시, 분, 초를 추출하는 예제입니다.
-- 위의 EXTRACT예제와 결과는 동일하나 TO_CHAR로 추출하는 예제입니다.
SELECT TO_CHAR(SYSDATE, 'YYYY') AS "년"  -- 2023
     , TO_CHAR(SYSDATE, 'MM') AS "월"    -- 04
     , TO_CHAR(SYSDATE, 'DD') AS "일"    -- 17
     , TO_CHAR(SYSDATE, 'HH24') AS "시"  -- 19
     , TO_CHAR(SYSDATE, 'MI') AS "분"   	-- 10
     , TO_CHAR(SYSDATE, 'SS') AS "초"    -- 27
  FROM DUAL;

TIMESTAMP

TIMESTAMP는 DATE와 비슷하지만 시간대 정보도 포함합니다. 기본형식은 'YYYY-MM-DD HH24:MI:SS.FF' 형식으로 저장이 되며 FF는 소수점 이하 초를 표시합니다. ORACLE에서 날짜정보를 등록할 때 DATE를 가장 많이 사용하지만 밀리 초까지의 정보를 저장하는 데이터가 필요하다면 TIMESTAMP를 사용하여야 합니다. TIMESTAMP는 INTERVAL 데이터 유형과 함께 사용할 수 있으며 특정 두 시간대의 시간 간격을 계산할 수 있습니다. TIMESTAMP는 ORACLE에서 제공하는 TO_CHAR, TO_TIMESTAMP를 통해 문자타입이나 날짜타입으로 형태를 변환할 수 있습니다.

-- 현재 TIMESTAMP 정보를 가져옵니다.
SELECT SYSTIMESTAMP FROM DUAL;   -- 2023-04-17 22:53:27.674677 +09:00

-- 현재 시간을 TIMESTAMP형으로 변환합니다.
SELET TO_TIMESTAMP(SYSDATE) FROM DUAL -- 2023-04-17 AM 00:00:00

-- 현재 TIMESTAMP 날짜에 1일 더합니다.
SELECT TO_TIMESTAMP(SYSDATE) + INTERVAL '1' DAY FROM DUAL;  -- 2023-04-18 AM 00:00:00

-- TIMESTAMP에서 년, 월, 일, 시, 분, 초를 추출하는 예제입니다.
SELECT EXTRACT(YEAR FROM SYSTIMESTAMP) AS "년"     -- 2023
     , EXTRACT(MONTH FROM SYSTIMESTAMP) AS "월"    -- 4
     , EXTRACT(DAY FROM SYSTIMESTAMP) AS "일"      -- 17
     , EXTRACT(HOUR FROM SYSTIMESTAMP) AS "시"     -- 19
     , EXTRACT(MINUTE FROM SYSTIMESTAMP) AS "분"   -- 10
     , EXTRACT(SECOND FROM SYSTIMESTAMP) AS "초"   -- 27
  FROM DUAL;
  
-- TIMESTAMP에서 년, 월, 일, 시, 분, 초, 밀리초를 추출하는 예제입니다.
SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY') AS "년"  -- 2023
     , TO_CHAR(SYSTIMESTAMP, 'MM') AS "월"    -- 04
     , TO_CHAR(SYSTIMESTAMP, 'DD') AS "일"    -- 17
     , TO_CHAR(SYSTIMESTAMP, 'HH24') AS "시"  -- 19
     , TO_CHAR(SYSTIMESTAMP, 'MI') AS "분"    -- 10
     , TO_CHAR(SYSTIMESTAMP, 'SS') AS "초"    -- 27
     , TO_CHAR(SYSTIMESTAMP, 'FF') AS "밀리초" -- 674677
     , TO_CHAR(SYSTIMESTAMP, '3FF') AS "밀리초" -- 674
  FROM DUAL;

INTERVAL YEAR TO MONTH

INTERVAL YEAR TO MONTH는 날짜 간의 연도와 월 간격을 나타내는 데이터 유형입니다. 이 데이터 유형은 'YYYY-MM'형식으로 저장되며 '년-월'의 데이터로 저장이 됩니다. 연도와 월의 간격을 나타내는 데이터 유형이기 때문에 일, 시, 분, 초의 데이터는 저장할 수 없습니다(INTERVAL YEAR TO SECOND 사용). 년, 월의 간격만 저장할 수 있습니다. 두 날짜 간의 기간을 계산하는 데 유용합니다.

-- 두 날짜간의 년, 월 수를 나타냅니다.
-- NUMTOYMINTERVAL함수는 숫자와 문자열을 인수로 받아INTERVAL Year to Month 데이터형으로 return합니다.
SELECT NUMTOYMINTERVAL(2, 'MONTH') FROM DUAL;   -- +00-02
SELECT NUMTOYMINTERVAL(2, 'YEAR') FROM DUAL;    -- +02-00
SELECT NUMTOYMINTERVAL(2*12+6, 'YEAR') FROM DUAL;    -- +02-06

-- 년, 월 수를 더합니다.
SELECT TO_DATE('20230417', 'YYYYMMDD') + NUMTOYMINTERVAL(2, 'YEAR') FROM DUAL; -- 2025/04/17
SELECT TO_DATE('20230417', 'YYYYMMDD') + NUMTOYMINTERVAL(2, 'MONTH') FROM DUAL; -- 2023/06/17

-- INTERVAL 데이터형에서 년, 월 추출하기 입니다.
SELECT EXTRACT(YEAR FROM INTERVAL '2-3' YEAR TO MONTH) FROM DUAL; -- 2
SELECT EXTRACT(MONTH FROM INTERVAL '2-3' YEAR TO MONTH) FROM DUAL; -- 3

INTERVAL DAY TO SECOND

INTERVAL YEAR TO SECOND는 일, 시, 분, 초, 소수점이하 초의 데이터를 저장하는 데이터 타입니다. INTERVAL YEAR TO SECOND는 년, 월의 데이터는 저장하지 않습니다. 이 데이터 유형은 'D HH:MI:SS.FF' 형식으로 저장되고 DATE 및 TIMESTAMP 유형과 함께 사용하여 날짜 간의 시간 간격을 계산할 수 있습니다. 서비스의 지속시간이나 이벤트의 간격등을 계산하는 데 사용할 수 있습니다.

-- 두 날짜간의 일, 시, 분, 초 및 밀리초 수를 나타냅니다.
-- NUMTODSINTERVAL함수는 숫자와 문자열을 인수로 받아INTERVAL Day to Second 데이터형으로 return합니다.
SELECT NUMTODSINTERVAL(10, 'DAY')  -- +10 00:00:00.000000
     , NUMTODSINTERVAL(2, 'HOUR')  -- +00 02:00:00.000000
     , NUMTODSINTERVAL(30, 'MINUTE')  -- +00 00:30:00.000000
     , NUMTODSINTERVAL(11.5, 'SECOND')  -- +00 00:00:11.500000
     , NUMTODSINTERVAL(10, 'DAY') + NUMTODSINTERVAL(2, 'HOUR') + NUMTODSINTERVAL(30, 'MINUTE') + NUMTODSINTERVAL(11.5, 'SECOND') -- +10 02:30:11.500000
  FROM DUAL;

-- 년, 월 수를 더합니다.
SELECT TO_DATE('20230417', 'YYYYMMDD') + INTERVAL '1 10:00:20' DAY TO SECOND FROM DUAL; -- 2023/04/18 10:00:20
SELECT TO_DATE('20230417', 'YYYYMMDD') + INTERVAL '1 10:00:20.20' DAY TO SECOND FROM DUAL; -- 2023/04/18 10:00:20.20


-- INTERVAL 데이터형에서 년, 월 추출하기 입니다.
SELECT EXTRACT(DAY FROM INTERVAL '1 10:00:20' DAY TO SECOND) FROM DUAL; -- 1
SELECT EXTRACT(SECOND FROM INTERVAL '1 10:00:20' DAY TO SECOND) FROM DUAL; -- 20

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE은 시간대 정보를 포함하는 날짜 정보를 나타내는 데이터 유형입니다. 이 데이터 유형은 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM' 형식으로 저장이 됩니다. 서로 다른 지역에서 저장된 시간 정보를 비교하는데 유용하며 글로벌 서비스를 제공할 때 각 지역의 시간을 저장하고 활용할 수 있습니다.

-- TIMESTAMP TIME ZONE을 이용한 나라별 시간 확인입니다.
-- V$TIMEZONE_NAMES로 각 나라별 TIME ZONE 정보를 확인하실 수 있습니다.
SELECT SYSTIMESTAMP  -- 2023/04/18 AM 0:14:33.606874 +09:00
     , CAST(SYSTIMESTAMP AS TIMESTAMP) AT TIME ZONE 'Europe/London'  -- 2023/04/17 PM 16:14:33.606645 +01:00
     , CAST(SYSTIMESTAMP AS TIMESTAMP) AT TIME ZONE 'America/New_York'  -- 2023/04/17 AM 11:14:33.606645 -04:00
  FROM DAUL

TIMESTAMP WITH LOCAL TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE은 TIMESTAMP WITH TIME ZONE과 유사하지만 시스템 시간대 정보를 사용합니다. TIMESTAMP WITH LOCAL TIME ZONE은 'YYYY-MM-DD HH:MI:SS.FF' 형식으로 저장이 됩니다. 이 데이터 유형은 글로벌 서비스를 제공할 때 각 지역의 로컬 시간대에 생성된 날짜나 시간을 저장하고 비교하는 데 사용할 수 있습니다.

-- TIMESTAMP TIME ZONE을 이용한 나라별 시간 확인입니다.
SELECT TO_TIMESTAMP_TZ('2023-04-17 20:00:00 Asia/Seoul', 'YYYY-MM-DD HH24:MI:SS TZR') -- 2023/04/17 PM 20:00:00.000000000 +09:00
     , TO_TIMESTAMP_TZ('2023-04-17 20:00:00 Europe/London', 'YYYY-MM-DD HH24:MI:SS TZR') -- 2023/04/17 PM 20:00:00.000000000 +01:00
     , TO_TIMESTAMP_TZ('2023-04-17 20:00:00 America/New_York', 'YYYY-MM-DD HH24:MI:SS TZR') -- 2023/04/17 PM 20:00:00.000000000 -04:00
  FROM DUAL;

오늘은 날짜 및 시간 데이터 유형에 대해 알아봤는데요. 쉽게 설명한다고 열심히 예제 만들고 했는데 이해가 잘 되실지 모르겠습니다. 유익한 정보가 되셨으면 합니다.