SQL 코딩테스트나 실무에서 날짜 관련 함수를 많이 접하실 것입니다. 그런데 서버환경에 따라 조금씩 문법들이 달라서 매우 헷갈리더라구요. MySQL, BigQuery, Hue(Impala) 서버에 따라 날짜관련 함수들을 비교하고 정리해보겠습니다. 관련 예시문제들에 적용해보았습니다.
📌 SQL 날짜함수 비교 정리
| 함수 | MySQL | BigQuery | Hue (Impala) |
| 현재 날짜 | CURDATE() | CURRENT_DATE() | CURRENT_DATE() |
| 날짜 포맷팅 | DATE_FORMAT(date, format) | FORMAT_DATE(format_string, date) | DATE_FORMAT(date, format) |
| 문자열을 날짜로 | STR_TO_DATE(string, format) | PARSE_DATE(format_string, string) | PARSE_TIMESTAMP(format, string) |
| 날짜 더하기 | DATE_ADD(date, INTERVAL n unit) | DATE_ADD(date, INTERVAL n DAY) | DATE_ADD(date, INTERVAL n DAY) |
| 날짜 빼기 | DATE_SUB(date, INTERVAL n unit) | DATE_SUB(date, INTERVAL n DAY) | DATE_SUB(date, INTERVAL n DAY) |
| 날짜 자르기 | (직접 지원하지 않음, 대체 가능) | DATE_TRUNC(date, part) | TRUNCATE(date, 'part') |
🙋♀️ Q1: 날짜 데이터를 특정 형식으로 변환하기
날짜를 YYYY-MM-DD 23:59:59 형태로 변환하는 방법은 다음과 같습니다.
- purchase_date가 문자열이기 때문에 우선 PARSE_TIMESTAMP로 타임스탬프로 변환한 뒤 FORMAT_TIMESTAMP로 형식을 지정합니다.
<예시 테이블> purchase_date 열 형태 m/d/y h:m:s
WITH transaction_tbl AS (
SELECT 211 AS user_id, 3003 AS item_id, 620000 AS actual_price, '03/01/2024 10:00:00' AS purchase_date
UNION ALL
SELECT 211, 4002, 98000, '03/05/2024 10:00:00'
UNION ALL
SELECT 222, 4002, 98000, '03/08/2024 10:00:00' UNION ALL
SELECT 256, 5003, 510000, '03/12/2024 10:00:00' UNION ALL
SELECT 231, 3003, 620000, '03/18/2024 10:00:00' UNION ALL
SELECT 256, 4003, 103000, '03/22/2024 10:00:00' UNION ALL
SELECT 245, 5002, 459000, '03/26/2024 10:00:00' UNION ALL
SELECT 222, 3004, 720000, '03/28/2024 10:00:00' UNION ALL
SELECT 211, 5002, 459000, '04/02/2024 10:00:00' UNION ALL
SELECT 278, 3004, 799000, '04/05/2024 10:00:00' UNION ALL
SELECT 231, 4002, 65000, '04/10/2024 10:00:00' UNION ALL
SELECT 256, 3003, 950000, '04/15/2024 10:00:00' UNION ALL
SELECT 290, 4003, 103000, '04/18/2024 10:00:00' UNION ALL
SELECT 222, 4003, 103000, '04/22/2024 10:00:00' UNION ALL
SELECT 211, 3004, 799000, '04/25/2024 10:00:00' UNION ALL
SELECT 256, 5002, 399750, '04/30/2024 10:00:00' UNION ALL
SELECT 222, 5003, 395250, '05/01/2024 10:00:00' UNION ALL
SELECT 211, 4003, 103000, '05/05/2024 10:00:00'
), user_info AS (
SELECT 211 AS user_id, 'Gwangju' AS city, 29 AS age, 'Female' AS gender UNION ALL
SELECT 231, 'Daejeon', 34, 'Male' UNION ALL
SELECT 245, 'Ulsan', 43, 'Female' UNION ALL
SELECT 256, 'Gwangju', 32, 'Male' UNION ALL
SELECT 278, 'Suwon', 27, 'Female' UNION ALL
SELECT 222, 'Gwangju', 40, 'Male' UNION ALL
SELECT 290, 'Daejeon', 30, 'Female'
), item_info AS (
SELECT 3003 AS item_id, 'Electronics' AS category, 'Laptop' AS item_name, 950000 AS list_price UNION ALL
SELECT 3004 AS item_id, 'Electronics' AS category, 'Smartwatch' AS item_name, 799000 AS list_price UNION ALL
SELECT 4003 AS item_id, 'Fashion' AS category, 'Sunglasses' AS item_name, 153000 AS list_price UNION ALL
SELECT 4002 AS item_id, 'Fashion' AS category, 'Hat' AS item_name, 98000 AS list_price UNION ALL
SELECT 5002 AS item_id, 'Home' AS category, 'Blender' AS item_name, 459000 AS list_price UNION ALL
SELECT 5003 AS item_id, 'Home' AS category, 'Vacuum Cleaner' AS item_name, 510000 AS list_price
)
SELECT *
FROM transaction_tbl
A1 .
bigquery에서는 날짜 형태를 변형할떄,
1) FORMAT_TIMESTAMP(format_string, date) 함수만 이용하면 오류발생!
-> purchase_date가 string 이기 때문에
2) 문자형 -> 날짜형 변수로 변환해주는 PARSE_DATE(format_string, string)함수를 이용해야함.
FORMAT_TIMESTAMP('%y-%m-%d %H:%M:%S' , PARSE_TIMESTAMP('%m/%d/%y %H:%M:%S', purchase_date))
3) DATE 함수를 통해 날짜형 변수로 지정해주면 추후 주 단위, 월 단위로 나눌때 오류가 발생하지 않습니다.
DATE(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', purchase_date))) AS formatted_date
SELECT * EXCEPT(purchase_date),
DATE(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', purchase_date))) AS formatted_date
FROM transaction_tbl
❗️ 헷갈릴 수 있는 함수 짚고 넘어가기
BigQuery에서 CAST 함수를 이용하여 문자열을 날짜형으로 변환할 수 있지 않나요?
CAST는 문자열이 기본 날짜 형식(예: YYYY-MM-DD)과 일치해야 합니다.
그렇지 않으면 PARSE_DATE나 PARSE_TIMESTAMP와 같은 함수를 사용하는 것이 적합합니다.
예시)
SELECT
CAST('2024-03-08' AS DATE) AS converted_date
🙋♂️ Q2: 주차별로 날짜 변환하기
날짜를 주 단위로 변환하려면 DATE_TRUNC 함수를 사용하여 formatted_date를 주 단위로 자릅니다.
A2. date_trunc 함수를 사용해 변환!
DATE_TRUNC(formatted_date, WEEK(MONDAY)) as week_date
, base as (
SELECT * EXCEPT(purchase_date),
DATE(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', PARSE_TIMESTAMP('%m/%d/%Y %H:%M:%S', purchase_date))) AS formatted_date
FROM transaction_tbl
)
SELECT *,
DATE_TRUNC(formatted_date, WEEK(MONDAY)) as week_date
FROM base
SQL..함수...문법 통일해줘 ㅠㅠ ㅎㅎ