CAST DATETIME EXTRACT SUBSTRING

CAST

문자열로 지정 날짜와 시각 기반의, 날짜 자료형과 타임스탬프 자료형의 데이터 생성

SELECT CAST('2016-10-30' AS date) AS 'date', 
	   CAST('12:00:00' AS time) AS 'time',
	   CAST('2016-10-30 12:00:00' AS datetime) AS 'datetime'; 

EXTRACT

타임스탬프 자료형 데이터에서 연도, 월 등 특정 필드 값을 추출

-- 연도 추출
SELECT EXTRACT(YEAR FROM STAMP) AS YEAR
FROM (SELECT CAST('2016-10-30' AS date) AS STAMP) AS T;

-- 월
, EXTRACT(MONTH FROM STAMP) AS MONTH 

-- 일
, EXTRACT(DAY FROM STAMP) AS DAY

-- 시
, EXTRACT(HOUR FROM STAMP) AS HOUR
FROM (SELECT CAST('2016-10-30 12:00:00' AS datetime) AS STAMP) AS T;

SUBSTRING

타임스탬프를 단순한 문자열로서 취급, 문자열 추출하는 함수

-- 연도
SELECT SUBSTRING(STAMP, 1, 4) AS YEAR
FROM (SELECT CAST('2016-10-30' as date) as STAMP) AS T; 

-- 월
SELECT SUBSTRING(STAMP, 6, 2) AS MONTH
FROM (SELECT CAST('2016-10-30' as date) as STAMP) AS T; 

--일
SELECT SUBSTRING(STAMP, 9, 2) AS YEAR
FROM (SELECT CAST('2016-10-30' as date) as STAMP) AS T; 

+ SUBSTR

• PostgreSQL, Hive, Redshift, SparkSQL → substring 함수 사용
• PostgreSQL, Hive, BigQuery, SparkSQL → substr 함수 사용

-- SUBSTRING 함수와 같은 결과
SELECT SUBSTR(STAMP, 1, 4) AS YEAR
FROM (SELECT CAST('2016-10-30' as date) as STAMP) AS T;