[Big query] 함수

날짜 다루기

1. DATE()

   SELECT
   DATE(2016,12,25) AS date_ymd,
   DATE(DATETIME '2016-12-25 23:59:59') AS date_dt,
   DATE(TIMESTAMP '2016-12-25 23:59:59', 'America/Los_Angeles') AS date_tstz;
   

2. DATETIME()

   SELECT
   DATETIME(2008,12,25,05,30,00) AS datetie_ymdhms,
   DATETIME(TIMESTAMP "2008-12-25 05:30:00+00", "America/Los_Angeles") AS datetime_tstz;
   -- 지역을 설정 가능, 서울은 Asia/Seoul
   

3. 날짜/ 시간 형태 변환 예시

   SELECT
   order_purchase_timestamp,
   DATE(order_purchase_timestamp) AS ord_date,
   DATETIME(order_purchase_timestamp) AS ord_dt,
   TIMESTAMP(order_purchase_timestamp) AS ord_date_ts,
   TIMESTAMP(DATE(order_purchase_timestamp)) AS ord_date_ts,
   TIME(order_purchase_timestamp) AS ord_time
   FROM `olist.olist_orders`
   ;
   

4. 날짜에서 일부분만 추출
   EXTRACT('추출할 부분' FROM '날짜컬럼') -> 결과는 날짜가 아닌 정수
   date , datetime, timestamp 모두 동일하게 사용

   dayofweek : 날짜/시간 중 요일 추출 (1 = 일요일, 2 = 월요일 ... ,7 = 토요일)
   week : 해당 날짜가 몇 번째 주인지 (일요일부터 시작) , 아래 예시에서 일요일인 11/26 부터 새로운 주가 시작됨
   week_monday : 해당 날짜가 몇 번째 주인지 (월요일부터 시작), 아래 예시에서11/26에 아직 새로운 주가 시작되지 않음

   -- 날짜 추출하기 EXTRACT
   SELECT
   order_purchase_timestamp,
   EXTRACT(YEAR FROM order_purchase_timestamp) YEAR,
   EXTRACT(QUARTER FROM order_purchase_timestamp) QUARTER,
   EXTRACT(MONTH FROM order_purchase_timestamp) MONTH,
   EXTRACT(DAY FROM order_purchase_timestamp) DAY,
   EXTRACT(DAYOFWEEK FROM order_purchase_timestamp) DAYOFWEEK,
   EXTRACT(WEEK FROM order_purchase_timestamp) WEEK,
   EXTRACT(WEEK(MONDAY) FROM order_purchase_timestamp) WEEK_MONDAY,
   EXTRACT(HOUR FROM order_purchase_timestamp) HOUR,
   EXTRACT(MINUTE FROM order_purchase_timestamp) MINUTE,
   EXTRACT(SECOND FROM order_purchase_timestamp) SECOND,
   FROM `olist.olist_orders`
   WHERE
   DATE(order_purchase_timestamp) = '2017-11-26' -- 일요일
   ;
   

5. 날짜에서 원하는 정보까지만 남기기
   DATE_TRUNC('날짜', '남기는부분')
   DATETIME_TRUNC('날짜', '남기는부분')
   TIMESTAMP_TRUNC('날짜', '남기는부분')
   원하는 부분까지만 남기고 나머지는 첫 날, 첫 시간등으로 반환

   week : 해당 주차의 첫 날(sun), 아래 예시에서 일요일인 11/26 그대로
   week(monday) : 해당 주차의 첫 날(mon), 아래 예시에서 해당 주의 첫날인 월요일 날짜 11/20으로 바뀜
   quarter : 해당 분기의 첫날, 아래 예시에서 해당 분기의 첫날인 10/1로 바뀜

   --날짜 남기기 TRUNC
   SELECT
   order_purchase_timestamp,
   TIMESTAMP_TRUNC(order_purchase_timestamp, YEAR) YEAR,
   TIMESTAMP_TRUNC(order_purchase_timestamp, QUARTER) QUARTER,
   TIMESTAMP_TRUNC(order_purchase_timestamp, MONTH) MONTH,
   TIMESTAMP_TRUNC(order_purchase_timestamp, DAY) DAY,
   TIMESTAMP_TRUNC(order_purchase_timestamp, WEEK) WEEK,
   TIMESTAMP_TRUNC(order_purchase_timestamp, WEEK(MONDAY)) WEEK_MONDAY,
   TIMESTAMP_TRUNC(order_purchase_timestamp, HOUR) HOUR,
   TIMESTAMP_TRUNC(order_purchase_timestamp, MINUTE) MINUTE,
   TIMESTAMP_TRUNC(order_purchase_timestamp, SECOND) SECOND
   FROM `olist.olist_orders`
   WHERE
   DATE(order_purchase_timestamp) = '2017-11-26' -- 일요일
   ;
   
   

6. EXTRACT 와 TRUNC 를 비교

   SELECT
   order_purchase_timestamp,
   EXTRACT(YEAR FROM order_purchase_timestamp) AS YEAR,
   TIMESTAMP_TRUNC(order_purchase_timestamp, YEAR) AS YEAR2,

   EXTRACT(MONTH FROM order_purchase_timestamp) AS MONTH,
   TIMESTAMP_TRUNC(order_purchase_timestamp, MONTH) AS MONTH2
   FROM `olist.olist_orders`
   WHERE
   DATE(order_purchase_timestamp) = '2017-11-26' -- 일요일
   ;
   

   extract 는 추출이고, trunc는 잘라내는 것이라 결과가 다름!
   extract의 결과는 숫자