[Oracle] Timezone 과 SYSTIMESTAMP 사용하기

Timezone 과 SYSTIMESTAMP 사용하기 | Oracle
2016.10.20 10:07:45 | 2022.11.29 09:52:10 | 393 | Aiden
❮ ❯
-- 1. SYSTIMESTAMP(0~9) 밀리초 자리수 0 ~ 9 지정 가능, 기본값 6
-----------------------------------------------------------------------------------------------------------------------------
SELECT  SYSTIMESTAMP
,       SYSTIMESTAMP(0)
,       SYSTIMESTAMP(9)
FROM DUAL; 


SYSTIMESTAMP                       SYSTIMESTAMP(0)                    SYSTIMESTAMP(9)                  
---------------------------------- ---------------------------------- ----------------------------------
16/09/20 13:00:16.365432000 +09:00 16/09/20 13:00:16.000000000 +09:00 16/09/20 13:00:16.365432000 +09:00




-- 2. 다양한 TIMESTAMP 조회 결과 (GMT가 -05:00인 Client로 테스트)
-----------------------------------------------------------------------------------------------------------------------------
SELECT  SYS_EXTRACT_UTC(SYSTIMESTAMP(0))    AS "UTC_TIME (Greenwich Mean Time)"
,       SYSTIMESTAMP(0)                     AS "SYSTIMESTAMP(0)"        -- UTC_TIME + DB GMT             적용된 시간 + Time Zone
,       CURRENT_TIMESTAMP(0)                AS "CURRENT_TIMESTAMP(0)"   -- UTC_TIME + Client Session GMT 적용된 시간 + Time Zone
,       LOCALTIMESTAMP(0)                   AS "LOCALTIMESTAMP(0)"      -- UTC_TIME + Client Session GMT 적용된 시간
,       SESSIONTIMEZONE                     AS "SESSIONTIMEZONE"        -- Client Session Time Zone
FROM DUAL; 


UTC_TIME (Greenwich Mean Time)  SYSTIMESTAMP(0)             CURRENT_TIMESTAMP(0)        LOCALTIMESTAMP(0)   SESSIONTIMEZONE
------------------------------- --------------------------- --------------------------- ------------------- --------------------
16/09/20 05:35:48               16/09/20 14:35:48 +09:00    16/09/20 00:35:48 -05:00    16/09/20 00:35:48   -05:00


* SYSTIMESTAMP(0)       : DB에 접속한 Client(쿼리Tool, WAS)의 Time Zone과 상관없이 DB의 Time Zone이 적용된 시간 <- DB의 Timezone으로 입력하고 싶을때 사용
* CURRENT_TIMESTAMP(0)  : DB에 접속한 Client(쿼리Tool, WAS)의 Time Zone이 적용된 시간 <- 현재의 Session Timezone으로 입력하고 싶을때 사용



-- 3. 다양한 TIMESTAMP 검색일시 가공하기
-----------------------------------------------------------------------------------------------------------------------------
SELECT  SYSTIMESTAMP(0)                                 AS NOW
,       ADD_MONTHS(SYSTIMESTAMP(0), 12)                 AS PLUS_YEAR
,       ADD_MONTHS(SYSTIMESTAMP(0), -12)                AS LAST_YEAR
,       SYSTIMESTAMP(0) - 1                             AS YESTERDAY
,       SYSTIMESTAMP(0) + 1                             AS TOMORROW

-- Timezone 유지되는 추천방식
,       SYSTIMESTAMP(0) + NUMTOYMINTERVAL(1, 'YEAR')    AS ADD_YEAR
,       SYSTIMESTAMP(0) + NUMTOYMINTERVAL(1, 'MONTH')   AS ADD_MONTH
,       SYSTIMESTAMP(0) + NUMTODSINTERVAL(1, 'DAY')     AS ADD_DAY
,       SYSTIMESTAMP(0) + NUMTODSINTERVAL(1, 'HOUR')    AS ADD_HOUR
,       SYSTIMESTAMP(0) + NUMTODSINTERVAL(1, 'MINUTE')  AS ADD_MINUTE
,       SYSTIMESTAMP(0) + NUMTODSINTERVAL(1, 'SECOND')  AS ADD_SECOND

-- 기준조건으로 일시 자르기
,       TRUNC(SYSTIMESTAMP(0))                          AS YYYYMMDD_000000 -- 오늘의 00시 00분 00초
,       TRUNC(SYSTIMESTAMP(0), 'DD')                    AS YYYYMMDD_000000 -- 오늘의 00시 00분 00초
,       TRUNC(SYSTIMESTAMP(0), 'MM')                    AS YYYYMM01_000000 -- 해당월의 1일 00시 00분 00초
,       TRUNC(SYSTIMESTAMP(0), 'YYYY')                  AS YYYY0101_000000 -- 해당년의 1월 1일 00시 00분 00초

-- 검색 기준 해당월의 1일 0시 00분 00초 부터 해당월의 말일의 23시 23분 59초
,       TRUNC(SYSTIMESTAMP(0), 'MM')                                                AS YYYYMM01_000000 -- 해당월의 01일 00시 00분 00초
,       TRUNC(ADD_MONTHS(SYSTIMESTAMP(0), 1), 'MM') - (1 / (24 * 60 * 60))          AS YYYYMMDD_235959 -- 해당월의 말일 23시 59분 59초 (다음월의 01일 00시 00분 00초에서 - 1초 방식)
,       TRUNC(ADD_MONTHS(SYSTIMESTAMP(0), 1), 'MM') - NUMTODSINTERVAL(1, 'SECOND')  AS YYYYMMDD_235959 -- 해당월의 말일 23시 59분 59초 (다음월의 01일 00시 00분 00초에서 - 1초 방식)
,       TRUNC(LAST_DAY(SYSTIMESTAMP(0))) + 0.99999                                  AS YYYYMMDD_235959 -- 해당월의 말일 23시 59분 59초 (해당월의 말일 00시 00분 00초에서 + 23:59:59 방식)
FROM DUAL;


-- WHERE 절의 FROM DATE TIME ~ TO DATE TIME 검색 예시
SELECT *
FROM   (SELECT TO_TIMESTAMP('20161031235959', 'YYYYMMDDHH24MISS') AS RQST_DT
        FROM DUAL) T
WHERE 1 = 1 
AND T.RQST_DT BETWEEN TRUNC(SYSTIMESTAMP(0), 'MM') AND TRUNC(LAST_DAY(SYSTIMESTAMP(0))) + 0.99999       -- FROM DATE 00:00:00 <= 대상일시 <= TO DATE 23:59:59 (해당 TO DATE의 23:59:59)
AND TRUNC(SYSTIMESTAMP(0), 'MM') <= T.RQST_DT AND T.RQST_DT < TRUNC(ADD_MONTHS(SYSTIMESTAMP(0), 1))     -- FROM DATE 00:00:00 <= 대상일시 <  TO DATE 00:00:00 (해당 TO DATA + 1일 00:00:00)
;





-- 2. TIMESTAMP WITH TIME ZONE과 TIMESTAMP WITH LOCAL TIME ZONE의 차이 

CREATE TABLE TEST
(ID VARCHAR2(10 CHAR) NOT NULL PRIMARY KEY
,REG_DT1 TIMESTAMP(0) WITH TIME ZONE
,REG_DT2 TIMESTAMP(0) WITH LOCAL TIME ZONE);

-- DB 세션으로 입력 (+09:00)
INSERT INTO TEST VALUES ('1', SYSTIMESTAMP(0), SYSTIMESTAMP(0));
COMMIT;


-- 세션 1 (+09:00)
SELECT * FROM TEST;

ID     WITH TIME ZONE                                    WITH LOCAL TIME ZONE
1	18/07/19 17:17:52.000000000 +09:00	18/07/19 17:17:52.000000000

WITH TIME ZONE -> 실제 입력된 Timezone으로 표시, 
WITH LOCAL TIME ZONE -> DB에 접속한 Session Timezone으로 변환하여 표시, 
위의 경우는 실제 입력한 세션 Timezone과 조회하는 세션 Timezone이 같으므로 똑 같이 표현되고 있다.  



-- 세션 2 (+08:00)
ID     WITH TIME ZONE                                    WITH LOCAL TIME ZONE
1	18/07/19 17:17:52.000000000 +09:00	18/07/19 16:17:52.000000000

WITH TIME ZONE -> 실제 입력된 Timezone으로 표시, 
WITH LOCAL TIME ZONE -> 현제 세션이 DB에 입력된 Timezone보다 1시간 빠른곳 이므로 +08:00의 Timezone에서는 실제 16:17:52에 입력된 시간으로 표현된다.



* TIMESTAMP WITH TIME ZONE
- TimeZone 정보 저장 
- 접속한 Session Timezone과 상관없이 입력당시의 Timezone 그대로 표현

* TIMESTAMP WITH LOCAL TIME ZONE
- TimeZone 정보 미저장 
- TIMESTAMP 데이터만 저장 
- 데이터 select 시 사용자(Client)의 Session Time 기준으로 TIMESTAMP 값이 자동 변환 되어 출력