231023 Oracle SQL 날짜타입

■ 날짜 타입
    A. SEVER 시간정보 : SYSDATE, SYSTIMESTAMP
    B. CLIENT 시간정보 : current_date, current_timestamp, localtimestamp

SELECT 
    SYSDATE , -- SEVER 시간정보
    SYSTIMESTAMP , 
    CURRENT_DATE , -- CLIENT 시간정보
    CURRENT_TIMESTAMP , 
    LOCALTIMESTAMP
FROM dual;

# 현재 접속한 지역의 TIME_ZONE 수정

ALTER SESSION SET TIME_ZONE = '+08:00' ;

# 수정 후 날짜 확인

SELECT 
    SYSDATE , -- SERER 시간정보
    SYSTIMESTAMP , 
    CURRENT_DATE , -- CLIENT 시간정보
    CURRENT_TIMESTAMP , 
    LOCALTIMESTAMP
FROM dual;

# 원위치

ALTER SESSION SET TIME_ZONE = '+09:00' ;

# 수정 후 날짜 확인

SELECT 
    SYSDATE , -- SERER 시간정보
    SYSTIMESTAMP , 
    CURRENT_DATE , -- CLIENT 시간정보
    CURRENT_TIMESTAMP , 
    LOCALTIMESTAMP
FROM dual;

 

DATE : 년월일	- 기존에 날짜 시간 초 정보는 년월일시분(초.5자리) -  SYSDATE, CURRENT_DATE
TIMESTAMP(9)  : 년월일시분(초.9자리)	- LOCALTIMESTAMP
TIMESTAMP(9) WITH TIME ZONE   : 년월일시분(초.9자리) TIMEZONE	- SYSTIMESTAMP, CURRENT_TIMESTAMP
TIMESTAMP(9) WITH LOCAL TIME ZONE  : 년월일시분(초.9자리)	- 보는 지역에 따라 날짜 시간 정보를 '자동'으로 정규화해서 보여준다.
INTERVAL YEAR TO MONTH : 기간을 나타내는 날짜 타입. 년수, 개월수
INTERVAL DAY TO SECOND : 기간을 나타내는 날짜 타입. 일수, 시분(초.9자리)

# TEST TABLE 생성 

CREATE TABLE hr.time_test (
    a  DATE,
    b  TIMESTAMP WITH TIME ZONE,
    c  DATE,
    d  TIMESTAMP WITH TIME ZONE,
    e  TIMESTAMP,
    f  TIMESTAMP WITH LOCAL TIME ZONE
);

desc hr.time_test

# data 로드 후 확인

INSERT INTO hr.time_test (a, b, c, d, e, f)
VALUES ( sysdate, systimestamp, current_date, current_timestamp, localtimestamp, localtimestamp );

SELECT * FROM hr.time_test;

# 시간 변경 후 TABLE 확인

ALTER SESSION SET TIME_ZONE = '+08:00';

SELECT * FROM hr.time_test;

# to_date()
    - 문자를 날짜형(date)으로 변환하는 함수 

SELECT to_date('20231023', 'yyyymmdd')
FROM dual;

SELECT to_date('2023-10-23 13:45:30', 'yyyy-mm-dd hh24:mi:ss')
FROM dual;

# to_timestamp
    - 문자를 날짜형(timestamp) 으로 변환하는 함수 
    - 초단위 'hh24:mi:ss.ff' ff를 뒤에 표현

SELECT to_timestamp('2023-10-23 13:45:30', 'yyyy-mm-dd hh24:mi:ss') to_timestamp
FROM dual;

# 초.9자리 표현

SELECT to_timestamp('2023-10-23 13:45:30.123456789', 'yyyy-mm-dd hh24:mi:ss.ff') to_timestamp_9
FROM dual;

# 초.6자리 표현

SELECT to_timestamp('2023-10-23 13:45:30.123456', 'yyyy-mm-dd hh24:mi:ss.ff') to_timestamp_6
FROM dual;

# to_timestamp_tz
    - 문자를 날짜형(timestamp with time zone) 으로 변환하는 함수 
    - '+09:00' 을 표현 : tzh:tzm

SELECT to_timestamp_tz('2023-10-23 13:45:30.123456 +09:00', 'yyyy-mm-dd hh24:mi:ss.ff tzh:tzm') as to_timestamp_tz
FROM dual;

# to_yminterval
    - 문자를 날짜형(INTERVAL YEAR TO MONTH) 으로 변환하는 함수 
    - 형변환 함수

# +10년
SELECT sysdate, sysdate + to_yminterval('10-00')
FROM dual;

# -10년
SELECT sysdate, sysdate - to_yminterval('10-00')
FROM dual;

# +4개월 
SELECT sysdate, sysdate + to_yminterval('00-04')
FROM dual;

# -1개월 
SELECT sysdate, sysdate - to_yminterval('00-01')
FROM dual;

# to_dsinterval
    - 문자를 날짜형(INTERVAL DAY TO SECOND) 으로 변환하는 함수 

# +(100일 00:00:00)
SELECT sysdate, localtimestamp + to_dsinterval('100 10:00:00')
FROM dual;

# -(100일 00:00:00)
SELECT sysdate, localtimestamp - to_dsinterval('100 10:00:00')
FROM dual;

# test table 생성

CREATE TABLE hr.time_test_2 (
    a  INTERVAL YEAR(3) TO MONTH,
    b  INTERVAL DAY(3) TO SECOND
);

# data 로드 후 조회

INSERT INTO hr.time_test_2 ( a, b ) VALUES( to_yminterval('10-02'), to_dsinterval('100 10:00:00') ) ;

SELECT * FROM hr.time_test_2;

# data 로드 후 확인

INSERT INTO hr.time_test_2 ( a, b ) VALUES( '10-11', '50 10:30:00' ) ;
SELECT * FROM hr.time_test_2;

# data 로드 
# 오류발생 : ORA-01843: not a valid month
    - 년수 개월수를 함께 사용할 때는 11개월까지만 입력 가능.

INSERT INTO hr.time_test_2 ( a, b ) VALUES( to_yminterval('10-12'), to_dsinterval('100 10:00:00') ) ;
SELECT to_yminterval('10-12') FROM dual;

# 날짜 계산 
    1. 날짜 + 일수 = 날짜
    2. 날짜 - 일수 = 날짜
    3. 날짜 + 날짜 = 오류
    4. 날짜 - 날짜 = 일수 
    5. 날짜 + 시간/24 = 날짜 시간 
    6. 날짜 + 분/(24*60) = 날짜 시간
    7. 날짜 + 초/(24*60*60) = 날짜 시간 
    8. 날짜 + interval year to month = 날짜
    9. 날짜 + interval day to second = 날짜
    
# to_char
    - 날짜형을 '문자형'으로 추출하는 날짜 함수

SELECT to_char(sysdate, 'yyyy mm dd hh24:mi:ss') 
FROM dual;

■ extract 
    - 날짜형을 '숫자형'으로 추출하는 날짜 함수 
    - 수치형으로 추출
    
# to_char VS extract

    A. year
SELECT to_char(sysdate, 'yyyy year'), extract(year from sysdate) 
FROM dual;

    B. month
SELECT to_char(sysdate, 'mm month mon'), extract(month from sysdate) 
FROM dual;

    C. day
SELECT to_char(sysdate, 'dd'), extract(day from sysdate) 
FROM dual;

    D. HH24 
    - 오류발생: extract(hour from sysdate) 
SELECT to_char(localtimestamp, 'hh24 hh12'), extract(hour from localtimestamp) 
FROM dual;

    E. minute
SELECT to_char(localtimestamp, 'mi'), extract(minute from localtimestamp) 
FROM dual;

    F. second 
SELECT to_char(localtimestamp, 'ss'), extract(second from localtimestamp) 
FROM dual;

    G. current_timestamp TIME_ZONE 시
SELECT to_char(current_timestamp, 'tzh'), extract(timezone_hour from current_timestamp) 
FROM dual;    

    G. current_timestamp TIME_ZONE 분
SELECT to_char(current_timestamp, 'tzm'), extract(timezone_minute from current_timestamp) 
FROM dual;  

SELECT to_char(current_timestamp, 'tzh:tzm'), extract(timezone_hour from current_timestamp)
FROM dual;

   
[문제] 각 사원의 last_name, hire_date 및 근속 연수를 출력하는 query를 작성합니다. 
1. 사원의 근속 연수가 10년 이상인 경우 '10 year of service' 를 출력합니다.
2. 사원의 근속 연수가 15년 이상인 경우 '15 year of service' 를 출력합니다
3. 사원의 근속 연수가 20년 이상인 경우 '20 year of service' 를 출력합니다.
4. 어떠한 조건과도 일치하지 않을 경우 'maybe next year!' 를 출력합니다. 
단, 근속 연수를 출력은 case, to_yminterval 을 사용하세요.

1# 기준날짜 

SELECT
    sysdate, 
    sysdate - to_yminterval('05-00') ,
    sysdate - to_yminterval('10-00') ,
    sysdate - to_yminterval('15-00')
FROM dual;

2# 근속연수 + case when-then

SELECT 
    last_name ,
    hire_date ,
    trunc(months_between(sysdate, hire_date)/12) 근속연수 , 
    CASE 
        WHEN sysdate - to_yminterval('20-00') >= hire_date THEN '20 year of service'
        WHEN sysdate - to_yminterval('15-00') >= hire_date THEN '15 year of service'
        WHEN sysdate - to_yminterval('10-00') >= hire_date THEN '10 year of service'
        ELSE 'maybe next year!'
    END awards
FROM hr.employees
ORDER BY 3 DESC;