EXTRACT() 날짜데이터 추출하기

** EXTRACT('추출할 부분' FROM '날짜 컬럼')

 

날짜에서 일부분만 추출하고 싶을 때 SQL에서는 EXTRACT 함수를 사용한다.

 

※ 추출할 부분 ※ 

 

YEAR : 연도

MONTH : 월

DAY : 일

DAYOFWEEK : 요일(0: 월요일 ~ 6: 토요일)

WEEK : 주(기본 일요일 시작)

WEEK(MONDAY) : 주(월요일 시작)

QUARTER : 분기

HOUR : 시

MINUTE : 분

SECOND : 초

-- 필요한 값만 추출하는 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-26' --일요일