시간 함수

모든 데이터는 시간과 연결되어 있습니다

시간 자료형은 크게 DATETIME, TIMESTAMP 등이 있습니다(DATE, TIME 등도 있지만 주요한 자료형은 앞에 두개)

• DATE : 날짜만 표시하는 데이터. 예 : 2021-04-02
• TIME : TIME만 표시하는 데이터. 예 : 23:04:10.00
• TIMESTAMP : 마이크로초 단위의 정밀도를 갖는 절대 시점. 타임존 정보 있음. 2021-04-02 14:04:10.00 UTC
• DATETIME : DATE와 TIME을 표시하는 데이터. 타임존 정보 없음 예: 2021-04-02 23:04:10.00

TIMESTAMP와 DATETIME 비교

아래 쿼리를 실행해서 차이를 확인할 수 있습니다

SELECT 
  CURRENT_TIMESTAMP() AS timestamp_col,
  DATETIME(CURRENT_TIMESTAMP(), 'Asia/Seoul') AS datetime_col

• TIMESTAMP는 맨 우측에 UTC라는 타임존이 표시됩니다
• DATETIME은 타임존 정보는 없고, Asia/Seoul로 변환해서 그 시간 정보만 저장되어 있습니다. 2021-06-18 우측에 T라는 단어가 보입니다.

시간의 단위

• millisecond(ms)

  • 시간의 단위, 천 분의 1초(1,000ms = 1초)
  • 우리가 아는 초보다 더 짧은 시간 단위
  • 눈을 깜빡이는 시간이 약 100ms
  • 빠른 반응이 필요한 분야에서 사용(초보다 더 정확하게)
  • Millisecond => TIMESTAMP => DATETIME으로 변경

• microsecond(µs)

  • 1/1,000ms, 1/1,000,000초

• TIMESTAMP_MILLIS : millisecond to Timestamp

• TIMESTAMP_MICROS : microsecond to Timestamp

SELECT 
  TIMESTAMP_MILLIS(1704176819711) AS milli_to_timestamp_value,
  TIMESTAMP_MICROS(1704176819711000) AS micro_to_timestamp_value,
  DATETIME(TIMESTAMP_MICROS(1704176819711000)) AS datetime_value;

TIMESTAMP

시간은 어떤 지역에서 동일하게 확인할 수 있어야 합니다. 이런 이유로 서버의 데이터는 타임스탬프로 형식으로 자주 저장됩니다.

타임존을 같이 보여주며, UTC 타임존을 사용합니다.

TIP

UTC : 국제적인 표준 시간의 기준으로 사용되는 시각, 한국은 UTC+9 hour(KST)로 표현합니다

DATETIME

DATE와 TIME을 표시하는 데이터로, 사람이 시간이라 생각하면 제일 바로 떠오르는 자료형

타임존을 사용해서 변환해서 저장시킵니다

DATETIME(타임스탬프_컬럼, 'Asia/Seoul')을 사용하면 한국 시간으로 변환

CURRENT_DATETIME

현재 DATETIME을 반환하는 함수입니다

• 문법 : CURRENT_DATETIME([time_zone]) : 현재 날짜

SELECT
  CURRENT_DATE(‘Asia/Seoul’)

EXTRACT

DATETIME에서 필요한 값(일자, 요일, 주차, 월, 분기, 연도, 시간 등)을 추출합니다

• 문법 : EXTRACT(part FROM datetime_expression) : DATETIME에서 값(일자, 요일, 주차, 월, 분기, 연도, 시간 등) 추출

SELECT 
  EXTRACT(HOUR FROM CAST('2019-12-25 14:00:00' AS DATETIME) AS hour

요일을 추출하고 싶은 경우, DAYOFWEEK을 작성하면 됩니다

• 한 주의 첫날이 일요일인 [1,7] 범위의 값을 반환합니다

SELECT 
  EXTRACT(DAYOFWEEK FROM CAST('2019-12-25 14:00:00' AS DATETIME) AS day_of_week

위 내용은 공식 Document (opens new window)에 나와있습니다

DATETIME_TRUNC

DATE과 HOUR만 남기고 싶은 경우 => 시간을 잘라야 합니다

• 문법 : DATETIME_TRUNC(datetime_col, HOUR)

2019-05-19 19:03:22을 HOUR로 자르면 2019-05-19 19:00:00가 됩니다

SELECT
  DATETIME_TRUNC(CAST('2019-12-25 14:00:00' AS DATETIME), HOUR) AS datetime_hour

위 내용은 공식 Document (opens new window)에 나와있습니다

DATETIME_DIFF

• 두 DATETIME의 차이를 알고 싶은 경우
• DATETIME_DIFF(첫 DATETIME, 두번째 DATETIME, 궁금한 차이)

SELECT
  DATETIME_DIFF(first_datetime, second_datetime, DAY) AS day_diff1,
  DATETIME_DIFF(second_datetime, first_datetime, DAY) AS day_diff2,
  DATETIME_DIFF(first_datetime, second_datetime, MONTH) AS month_diff,
  DATETIME_DIFF(first_datetime, second_datetime, WEEK) AS week_diff,
FROM (
  SELECT
    DATETIME "2024-04-02 10:20:00" AS first_datetime,
    DATETIME "2021-01-01 15:30:00" AS second_datetime,
  )

DATETIME_ADD

• 하나의 DATETIME에서 특정 Interval을 더하고 싶은 경우
• 빼고 싶은 경우엔 DATETIME_SUB 사용

SELECT
  DATETIME "2024-01-01 15:30:00" AS original_date,
  DATETIME_ADD(DATETIME "2024-01-01 15:30:00", INTERVAL 10 MINUTE) AS later;

/*-----------------------------+------------------------*
 | original_date               | later                  |
 +-----------------------------+------------------------+
 | 2024-01-01T15:30:00         | 2024-01-01T15:40:00    |
 *-----------------------------+------------------------*/