SQL 기본, 날짜/시간 표현

 

//기본 형태 "2021-07-26 10:25:25"
SELECT NOW();
 
//날짜 "2021-07-26"
SELECT DATE(NOW());
 
//시간 "10:25:25"
SELECT TIME(NOW());
 
//년 "2021"
SELECT YEAR(NOW());
 
//월 "7"
SELECT MONTH(NOW());
 
//날짜 포맷 변경 "2021/07/26"
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');
 
//시간 포맷 변경 "10 25 25"
SELECT DATE_FORMAT(NOW(), '%H %i %s');

// NOW 기준, 하루
SELECT * FROM TABLE_NAME WHERE CREATED_DATE BETWEEN DATE_ADD(NOW(), INTERVAL -1 DAY ) AND NOW();
 
// NOW 기준, 일주일
SELECT * FROM TABLE_NAME WHERE CREATED_DATE BETWEEN DATE_ADD(NOW(), INTERVAL -1 WEEK ) AND NOW();
 
// NOW 기준, 한달
SELECT * FROM TABLE_NAME WHERE CREATED_DATE BETWEEN DATE_ADD(NOW(), INTERVAL -1 MONTH ) AND NOW();
 
//특정 날짜 지정 (2021년 7월 26일)
SELECT * FROM TABLE_NAME WHERE CREATED_DATE >= '2021-07-26 00:00:00' AND CREATED_DATE <= '2021-07-27 00:00:00';
 
//특정 날짜 지정2 (2021년 7월 26일)
SELECT * FROM TABLE_NAME WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m-%d') = '2021-07-26';
 
//날짜 조건
SELECT * FROM TABLE_NAME WHERE DATE(CREATED_DATE) BETWEEN '2021-01-01' AND '2021-12-31';
SELECT * FROM TABLE_NAME WHERE DATE(CREATED_DATE) >= '2021-01-01' AND DATE(a.CREATED_DATE) <= '2021-12-31';
 
//월별 조건
SELECT * FROM TABLE_NAME WHERE DATE_FORMAT(CREATED_DATE, '%Y-%m') BETWEEN '2021-01' AND '2021-07';

 

Google BigQuery 날짜 및 시간 함수는 아래 참조

표준 SQL의 날짜 함수  |  BigQuery  |  Google Cloud