SQL 실습02-날짜형식

EXTRACT

-- 원하는 형식으로 extract
SELECT
  order_purchase_timestamp,
  EXTRACT(YEAR FROM order_purchase_timestamp) AS YEAR,
  EXTRACT(QUARTER FROM order_purchase_timestamp) AS QUARTER,
  EXTRACT(MONTH FROM order_purchase_timestamp) AS MONTH,
  EXTRACT(DAY FROM order_purchase_timestamp) AS DAY,
  EXTRACT(DAYOFWEEK FROM order_purchase_timestamp) AS DAYOFWEEK,
  EXTRACT(WEEK FROM order_purchase_timestamp) AS WEEK,
  EXTRACT(WEEK(MONDAY) FROM order_purchase_timestamp) AS WEEK_MONDAY,
  EXTRACT(HOUR FROM order_purchase_timestamp) AS HOUR,
  EXTRACT(MINUTE FROM order_purchase_timestamp) AS MINUTE,
  EXTRACT(SECOND FROM order_purchase_timestamp) AS SECOND
FROM `olist.olist_orders`
WHERE DATE (order_purchase_timestamp) = '2017-11-28' -- 일요일
;

TIMESTAMP_TRUNC

-- 원하는 정보까지만 남기는 DATE/DATETIME/TIMESTAMP_TRUNC
SELECT
  order_purchase_timestamp,
  TIMESTAMP_TRUNC (order_purchase_timestamp, YEAR) AS YEAR,
  TIMESTAMP_TRUNC (order_purchase_timestamp, QUARTER) AS QUARTER,
  TIMESTAMP_TRUNC (order_purchase_timestamp, MONTH) AS MONTH,
  TIMESTAMP_TRUNC (order_purchase_timestamp, DAY) AS DAY,
  TIMESTAMP_TRUNC (order_purchase_timestamp, WEEK) AS WEEK,
  TIMESTAMP_TRUNC (order_purchase_timestamp, WEEK(MONDAY)) AS WEEK_MONDAY,
  TIMESTAMP_TRUNC (order_purchase_timestamp, HOUR) AS HOUR,
  TIMESTAMP_TRUNC (order_purchase_timestamp, MINUTE) AS MINUTE,
  TIMESTAMP_TRUNC (order_purchase_timestamp, SECOND) AS SECOND
FROM `olist.olist_orders`
WHERE DATE (order_purchase_timestamp) = '2017-11-26'
;


-- 둘 비교
SELECT
  order_purchase_timestamp,
  EXTRACT (YEAR FROM order_purchase_timestamp) AS YEAR,
  TIMESTAMP_TRUNC (order_purchase_timestamp, YEAR) AS YEAR2
  
FROM `olist.olist_orders`
WHERE DATE (order_purchase_timestamp) = '2017-11-26'
;

이 글은 제로베이스 데이터 분석 취업 스쿨의 강의자료 일부를 발췌하여 작성되었습니다.