• Home
  • About
    • DEOKHEE LEE photo

      DEOKHEE LEE

      Data science archive

    • Learn More
    • Email
    • Facebook
    • Instagram
    • Github
  • Posts
    • All Posts
    • All Categories
    • All Tags
  • Projects

[SQL] Type

01 Jul 2018

Reading time ~6 minutes

형 변환 함수

암시적 형 변환

  • 비교하는 대상의 Type이 서로 다르면 형 불일치 발생 -> 세션 발생(프로그램단에서 오류 발생)

테이블의 구조를 먼저 확인하는 습관을 가지자 : DESC 테이블명

SELECT *
FROM employees
WHERE department_id = 10;
          number    = number  -- OK
          
      department_id = '10';
          number    = char    --형 불일치 -> char타입이 number타입으로 암시적 형변환


날짜 타입의 형변환

  • 지역과 언어에 맞게 문자 날짜로 써주면 암시적으로 date형으로 형변환이 일어남
SELECT *
FROM employees
WHERE hire_date = '2001/01/01'; -- OK
        date    =     char
        
      hire_date = '01-01-2001'; -- 터무늬없는 날짜타입은 X


두개의 컬럼이 합쳐졌을 경우 (연결연산자 사용 시)

  • 컬럼의 타입에 char Type이 있을 경우 해당 컬럼은 char Type으로 바뀜
  • 날짜+숫자의 경우도 char Type -> 연결연산자로 합칠 경우 대부분 char Type이 됌
SELECT last_name || salary
          char   || number  -- char Type
          
FROM employees;

SELECT 1+'2'
FROM dual;

>3 --'2'는 문자형이지만 숫자로 암시작 형변환이 일어나서 계산이 가능함


TO_CHAR() char형으로 변환하는 함수

  • number->char, date->char로 형변환 하는 함수

  • 보고서 작성 등 문서 작업에 주로 사용하면 유용하다

  • 참고
    TO_CHAR(): 여러 형태의 날짜를 출력할 때 사용하는 용도
    TO_DATE(): 날짜를 입력할 때와 날짜를 조회할 때 사용하는 용도
Ex. TO_DATE() - CHAR(첫 인자)를 원하는 DATE형식(2번째 인자)으로 바꾸어줌

SELECT TO_DATE('20180530 00:00:00.00000','yyyymmdd hh24:mi:ss.sssss')
FROM dual;


날짜 표현 (DATE -> CHAR)

연도, 달
SELECT to_char(sysdate,'yyyy year mm month mon')
FROM dual;

>2018 twenty eighteen 05 5월 5월


참고, RR타입과 YY타입
  • 2000년도 이전에는 Storage 문제로 2000년 이후를 표현하지 않음
  • 그래서 나온 것이 RR타입과 YY타입
SELECT TO_CHAR(TO_DATE('95-10-27','rr-mm-dd'),'yyyy')
FROM dual;

>1995

SELECT TO_CHAR(TO_DATE('95-10-27','yy-mm-dd'),'yyyy')
FROM dual;

>2095

하지만, 연도 2자리만 썼을 때 생기는 문제가 해결 된 것은 아님

  • YY 타입은 기준이 현재 연도로 따라감
  • RR 타입은 상황에 따라 달라짐
　 　 　
  지정된 연도(x)  
현재 연도(y) 0 ~ 49 50 ~ 99
0 ~ 49 반환 날짜는 현재세기를 반영 반환 날짜는 이전 세기를 반영
50 ~ 99 반환 날짜는 이후 세기를 반영 반환 날짜는 현재 세기를 반영
현재 연도 데이터입력날짜
(지정된연도)		 YY
(현재연도 반영)		 RR
1994년 95-10-27 1995 1995
1994년 17-10-27 1917 2017
2001년 17-10-27 2017 2017
2048년 52-10-27 2057 1952
2051년 47-10-27 2047 2147

그래서, 현장에서는 RR타입을 쓰지 않는다!

날짜 데이터를 입력 할 때는 연도 4자리를 표현해서 사용하자!

일, 주, 분기
  • ‘d’ 는 요일별로 정렬할 때 유용하게 사용됌
SELECT to_char(sysdate,'ddd dd d q"분기" day dy')
FROM dual;

>149 29 3 2분기 화요일 화
--1년 중 149일 째, 한 달에 29일 째, 일주일에 3일 째


SELECT TO_CHAR(sysdate,'ww w')
FROM dual;

>22 5
--1년 중 22주 째, 한 달 중 5주 째


시간
SELECT to_char(sysdate,'hh24:mi:ss,sssss am')
FROM dual;

>10:57:33,39453 오전 --24시간 기준으로 시간 표시


BC, 세기
SELECT TO_CHAR(sysdate,'bc scc yyyy')
FROM dual;

>서기 21 2018


fm -선행되는 0을 지워줌
SELECT to_char(sysdate,'mm')
FROM dual;

>05

SELECT to_char(sysdate,'fm mm')
FROM dual;

>5


spth -서수 단위로 사용하고 싶을 시
SELECT to_char(sysdate,'ddspth')
FROM dual;

>twenty-ninth


요일 순서 정렬 팁
Ex. 입사 날짜를 요일로 추출

SELECT to_char(hire_date,'day')
FROM employees
ORDER BY to_char(hire_date,'d'); --'d'를 사용하여 날짜순으로 sort
  • 위 코드는 일요일 부터 정렬됌
  • 하지만, 월요일을 먼저 정렬하고 싶다면?
SELECT to_char(hire_date,'day')
FROM employees
ORDER BY to_char(hire_date-1, 'd');


숫자 표현 (NUMBER -> CHAR)

자리 수 구분
SELECT TO_CHAR(123456789, '999,999,999.00'), --9는 자리수 표현(빈 자리는 자동 삭제)
TO_CHAR(123456789, '000,000,999.00'), -- 0은 (빈 자리 0으로 메꿈)
TO_CHAR(123456789, '999g999g999d00'), -- g,d는 그 나라의 표현법대로 자동 변환해줌
TO_CHAR(1234567, '999.00')-- 자리수 초과시 #표시되니 주의
FROM dual;

>1,234,567.00
>001,234,567.00
>1,234,567.00
>#######


화폐 표현
SELECT TO_CHAR(12345, '$999,999.00'), --$는 Oracle 에서 지원함
TO_CHAR(12345, 'l999,999.00') -- 자국 통화 부호(l)
FROM dual;

>$12,345.00
>￦12,345.00


날짜 환경 및 형태

지역, 언어(SESSION) 변경

  • 원하는 지역과 언어로 변경하고 싶을 때 ALTER SEESION SET 사용
  • ALTER SESSION SET은 ORACLE 에 접속한 ‘지금’만 영향을 줌
  • SESSION을 변경하고 싶지 않다면, TO_DATE()를 사용한다. (문제 27 참고)
Ex.

SELECT * FROM nls_session_parameters;

ALTER SESSION SET NLS_TERRITORY=KOREA;
ALTER SESSION SET NLS_LANGUAGE =KOREAN;

ALTER SESSION SET NLS_TERRITORY = GERMANY;
ALTER SESSION SET NLS_LANGUAGE= GERMAN;

ALTER SESSION SET NLS_LANGUAGE =JAPANESE;
ALTER SESSION SET NLS_TERRITORY=JAPAN;

ALTER SESSION SET NLS_LANGUAGE =FRENCH;
ALTER SESSION SET NLS_TERRITORY=FRANCE;

ALTER SESSION SET NLS_TERRITORY=AMERICA;
ALTER SESSION SET NLS_LANGUAGE =AMERICAN;

ALTER SESSION SET NLS_TERRITORY=china;
ALTER SESSION SET NLS_LANGUAGE = 'simplified chinese';

SELECT employee_id, 
      to_char(salary,'l999g999d00'),
      to_char(hire_date, 'YYYY-MONTH-DD DAY')
FROM employees;

Ex.

SELECT sysdate
FROM dual;

>18/05/29


--ALTER SESSION SET 선언
ALTER SESSION SET NLS_DATE_FORMAT='yyyymmdd'; --지금 세션 레벨에서는 'yyyymmdd' 형태로 쓰겠다.

SELECT sysdate
FROM dual;

>20180529

--Developer에서 선언하였으면, Developer에만 영향을 주고(현재 창), Run SQL command line 에서는 영향을 주지 않음


날짜 형태 (DATE TYPE)

SERVER/CLIENT 날짜 타입의 종류 (ANSI 표준)
  • date
  • timestamp: 시간을 최대 9자리까지 볼 수 있음(default 6자리)
    • timestamp with time zone: time zone 까지 저장하는 날짜 타입
    • timestamp with local time zone: with time zone에서 보는 사람에 맞게 자동으로 정규화까지 해주는 날짜 타입


서버 시간과 세션 시간을 구분해서 사용할 줄 알아야


  1. 서버 시간대 표시
    • sysdate
    • systimestamp
  2. SESSION 시간대 표시 (CLIENT 시간대)
    • current_date
    • current_timestamp
    • localtimestamp


SELECT sysdate, -- date (SERVER)
systimestamp, -- timestamp (SERVER)
current_date, -- timestamp with time zone (CLIENT)
current_timestamp, -- timestamp with time zone (CLIENT)
localtimestamp -- timestamp with local time zone (CLIENT)

FROM dual;

>18/05/29
>18/05/29 14:12:39.079000000 +09:00
>18/05/29
>18/05/29 14:12:39.000000000 ASIA/SEOUL
>18/05/29 14:12:39.000000000

Ex. 한국 지역이지만 싱가폴 시간대에서 작업 하고 싶을 경우,

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

SELECT sysdate,
systimestamp, 
current_date, 
current_timestamp, 
localtimestamp

FROM dual;

>18/05/29
>18/05/29 14:20:24.031000000 +09:00
>18/05/29
>18/05/29 13:20:24.000000000 +08:00
>18/05/29 13:20:24.000000000

Ex. 초 단위 출력 예시

SELECT TO_CHAR(sysdate,'yyyymmdd hh24:mi:ss.sssss'), 
systimestamp, 
TO_CHAR(current_date,'yyyymmdd hh24:mi:ss.sssss'),
current_timestamp, localtimestamp
FROM dual;


SESSION 바꾸는 방법

ALTER SESSION SET TIME_ZONE = 'Asia/Seoul';

정확한 이름을 모르면 아래 코드로 찾아서 설정할 수 있다.

SELECT *
FROM v$timezone_names;


TO_INTERVAL() - 날짜 사이의 기간을 명시하는 날짜 타입
  1. Interval year to month
    • 연 수, 개월 수만 명시 가능
    • TO_YMINTERVAL(): CHAR -> INTERVAL year to month 형변환 함수
  2. Interval day to second
    • 일(day), 시, 분, 초 이하 9자리 까지 기간 명시 가능
    • TO_DSINTERVAL(): CHAR -> INTERVAL day to second 형변환 함수
  • 제품의 유효기간, 대출 상품 등에 사용할 수 있음
날짜 사이의 기간을 명시하는 함수
  • TO_YMINTERVAL(): 년도와 개월 기준으로 사이의 기간을 파악할 시
  • TO_DSINTERVAL(): 일과 시간 기준으로 사이의 기간을 파악할 시
  • 용법은 아래 예시 참고
Ex. TO_YMINTERVAL() 사용 예시

SELECT sysdate+to_yminterval('10-02')
                            '년-개월수'
FROM dual;

--TO_YMINTERVAL 을 사용하면, ADD_MONTHS()로 개월 수를 구해서 계산 할 필요가 없다.

Ex. TO_DSINTERVAL() 사용 예시

SELECT sysdate+TO_DSINTERVAL('100 00:00:00')
                              '일 시간'
FROM dual;


EXTRACT() 숫자 형태로 날짜 추출하는 함수
  • 숫자 형태로 추출하기 때문에 따로 형을 바꿀 필요가 없다!


Ex. 사용 예시

SELECT EXTRACT(year from sysdate)
FROM dual;

SELECT EXTRACT(month from sysdate)
FROM dual;

SELECT EXTRACT(day from sysdate)
FROM dual;

SELECT EXTRACT(hour from localtimestamp)
FROM dual;

SELECT EXTRACT(minute from localtimestamp)
FROM dual;

SELECT EXTRACT(second from localtimestamp)
FROM dual;

SELECT EXTRACT(timezone_region from current_timestamp)
FROM dual;

SELECT EXTRACT(timezone_hour from current_timestamp)
FROM dual;

SELECT EXTRACT(timezone_minute from current_timestamp)
FROM dual;


TO_TIMESTAMP() 소수점의 초(S)를 확인하는 함수
  • TO_TIMESTAMP() 함수를 써서 초의 소수점 9자리까지 확인할 수 있음
  • DATE형식을 사용할 때, ff를 사용 (TO_DATE() 함수는 ss를 사용)
Ex. 18년 1월 1일의 하루의 시작과 끝을 표시


SELECT TO_TIMESTAMP('20180101 00:00:00.00000000','yyyymmdd hh24:mi:ss.ff')
TO_TIMESTAMP('20180101 23:59:59.999999999','yyyymmdd hh24:mi:ss.ff')
FROM dual;
  • 소수점 n자리만 추출하고 싶다면?
    • TO_CHAR()를 써서 활용
Ex. TO_CHAR()를 사용하여 현재 시간의 소수점 2자리 추출

SELECT TO_CHAR(systimestamp, 'ss.ff2')
FROM dual;

>21.06

Ex. TO_CHAR()와 TO_TIMESTAMP()와 함께 사용

SELECT TO_CHAR(TO_TIMESTAMP('20180101 12:00:00.999999999','yyyymmdd hh24:mi:ss.ff'),
'yyyymmdd hh24:mi:ss.ff5')
FROM dual;

>20180101 12:00:00.99999



sqldatatype SQL Share Tweet +1