TIMESTAMP:UTC(또는 특정 기준)부터 경과한 시간을 나타내는 값 (ex. `2024-12-31 14:59:58 UTC`)
TimeZone 정보 있음! (스탬프 찍었으니까)
대부분 테이블에 시간 데이터가 TIMESTAMP로 저장된 경우가 많은데, DATETIME으로 변환 자유자재로 할 줄 알아야 함!
⭐타임존(TimeZone)개념을 이해해야 함!!!
GMT(Greenwich Mean Time) - 영국 그리니치 천문대(경도 0°)를 기준으로 지역에 따른 시간 차이를 조정하기 위해 생긴 구분선 - ex) 한국 시간 = GMT+9 UTC(Universal Time Coordinated, 협정세계시) - 국제적인 표준 시간. 최근에는 GMT보다 UTC를 많이 사용함. (차이는 거의 미약하긴 함) - ex) 한국 시간 = UTC+9 - '타임존이 존재한다' = 특정 지역의 표준 시간대가 존재한다
2) 초(sec)보다 더 짧은 시간 단위도 있음
Millisecond(ms)
1/1000초 (즉, 1초 = 1,000ms)
1초보다 더 정확하고 빠른 반응이 필요한 분야에 사용됨 (ex. 개발에서 서버 요청 처리 순서 등)
`TIMESTAMP_MILLIS`를 사용해 TIMESTAMP 타입으로 변환 가능
Microsecond(μs)
1/1000ms (즉, 1초 = 1,000ms = 1,000,000μs)
`TIMESTAMP_MICROS`를 사용해 TIMESTAMP 타입으로 변환 가능
millisecond보다는 덜 흔하지만, 혹시 회사에서 이렇게 저장된 경우엔 알고 있어야 함
TIMESTAMP와 DATETIME 비교
1)시간 데이터가 TIMESTAMP로 저장된 경우가 많은데,DATETIME으로 변환자유자재로 할 줄 알아야 함!
`DATETIME(타임스탬프 정보, 타임존 정보)`와 같이 쿼리 작성!
보통millisecond➔ TIMESTAMP➔DATETIME으로 변환해서 사용함
ex) 1704176819711 ms➔ 2024-01-02 15:26:59 (DATETIME) 로 바꾸고 싶은 경우
단, TIMESTAMP ➔ DATETIME할 때타임존 정보 반드시 입력! (ex. `'Asia/Seoul'`)
타임존 없이 변환하면, 데이터가 잘못 나오게 됨(ex. 오후 3시에 구매한 건데 오전 6시라고 나와버림)
SELECT
TIMESTAMP_MILLIS(1704176819711) AS milli_to_timestamp_value, -- 밀리초 → TIMESTAMP
TIMESTAMP_MICROS(1704176819711000) AS micro_to_timestamp_value, -- 마이크로초 → TIMESTAMP (밀리초에서 000 붙이면 됨)
DATETIME(TIMESTAMP_MILLIS(1704176819711)) AS datetime_value, --❎TIMESTAMP → DATETIME
DATETIME(TIMESTAMP_MILLIS(1704176819711), 'Asia/Seoul') AS datetime_value_asia --✅이게 올바른 변환
-- 또는 TIMESTAMP_MICROS(1704176819711000) 처럼 마이크로초 사용해도 됨
2) ⭐TIMESTAMP와 DATETIME이 헷갈린다면?
동일한 시간에 대해 TIMESTAMP와 DATETIME을 함께 출력해보면...(참고로, `CURRENT_TIMESTAMP()`는 현재 타임스탬프 정보를 알려줌)
TIMESTAMP : 타임존이UTC라고 나옴. `한국 시간 - 9시간`으로 나옴 (=01시39분)
DATETIME : 타임존 없어서 그냥T(=time)라고 나옴.한국 시간과 동일하게 나옴 (=10시39분)
2. 시간 데이터 관련 함수
CURRENT_DATETIME
`CURRENT_DATETIME()` : 현재 DATETIME을 출력 (타임존 정보를 넣을 수도 있음)
ex) 한국 타임존 넣은 경우와 넣지 않은 경우 9시간 차이 나는 것을 볼 수 있음
`CURRENT_DATE()` : 현재 날짜를 출력 (타임존 정보를 넣을 수도 있음)
EXTRACT
`EXTRACT(추출할_내용 FROM 데이트타임_표현)` : DATETIME에서 특정 내용만 추출해줌
# 쿼리 작성하는 목표, 확인할 지표 : 포켓몬의 수
# 쿼리 계산 방법 : count
# 데이터의 기간 : 2023년 1월!
# 사용할 테이블 : trainer_pokemon
# JOIN KEY : 없음
# 데이터의 특징 :
-- catch_date : DATE 타입
-- catch_datetime : TIMESTAMP 타입 (이런 오차 있을 수 있으니 데이터 확인이 필수!!)
-- 또, catch_date가 KR 기준인지 UTC 기준인지도 확인이 필요함;;
# 0. 데이터 검증을 위한 쿼리
SELECT
*
FROM(
SELECT
id,
catch_date,
DATE(DATETIME(catch_datetime, "Asia/Seoul")) AS catch_datetime_kr_date
-- TIMESTAMP > DATETIME > DATE 순으로 변환
FROM basic.trainer_pokemon
)
WHERE
catch_date != catch_datetime_kr_date;
# 1. 본 문제 해결을 위한 쿼리
SELECT
COUNT(DISTINCT(id)) AS cnt -- DISTINCT는 혹시 모를 id 중복을 대비해서 적은 것
FROM basic.trainer_pokemon
WHERE
EXTRACT(YEAR FROM DATETIME(catch_datetime, "Asia/Seoul")) = 2023
-- catch_datetime이 TIMESTAMP 형태로 저장되어있으므로 DATETIME으로 변경해서 사용
AND EXTRACT(MONTH FROM DATETIME(catch_datetime, "Asia/Seoul")) = 1
/* 내 풀이
SELECT
EXTRACT(MONTH FROM catch_date) AS catch_month,
COUNT(id) AS cnt
FROM basic.trainer_pokemon
GROUP BY
catch_month
HAVING
catch_month = 1
*/
문제 2. 배틀이 일어난 시간(battle_datetime)을 기준으로, 오전 6시에서 오후 6시 사이에 일어난 배틀의 수를 계산해주세요.
# 쿼리 작성하는 목표, 확인할 지표 : 배틀의 수
# 쿼리 계산 방법 : count
# 데이터의 기간 : 6시 - 18시
# 사용할 테이블 : battle
# JOIN KEY : 없음
# 데이터의 특징 :
-- battle_date : battle_datetime 기반으로 만들어진 DATE 타입
-- battle_datetime : DATETIME 타입
-- battle_timestamp가 UTC 기준인데 9시간 차이나는 걸 보니 KR 기준 잘 되어있음
# 0. battle_datetime, battle_timestamp 검증
SELECT
-- id,
-- battle_datetime,
-- DATETIME(battle_timestamp, "Asia/Seoul") AS battle_timestamp_kr
COUNTIF(battle_datetime = DATETIME(battle_timestamp, "Asia/Seoul")) AS battle_datetime_same_battle_timestamp_kr,
COUNTIF(battle_datetime != DATETIME(battle_timestamp, "Asia/Seoul")) AS battle_datetime_diff_battle_timestamp_kr
FROM basic.battle;
# 1. 본 문제 풀이 (1)
SELECT
COUNT(DISTINCT(id)) AS battle_cnt
FROM basic.battle
WHERE
EXTRACT(HOUR FROM battle_datetime) < 18
AND EXTRACT(HOUR FROM battle_datetime) >= 6
# 1. 본 문제 풀이 (2)
SELECT
COUNT(DISTINCT(id)) AS battle_cnt
FROM basic.battle
WHERE
EXTRACT(HOUR FROM battle_datetime) BETWEEN 6 AND 17
-- EXTRACT의 결과가 숫자형이니까 BETWEEN 사용 가능
/* 내 풀이
SELECT
COUNT(DISTINCT(id)) AS cnt
FROM basic.battle
WHERE
EXTRACT(HOUR FROM battle_datetime) <= 17
AND EXTRACT(HOUR FROM battle_datetime) >= 6
*/
문제 2-1 (보너스). 배틀이 일어난 시간(battle_datetime)을 기준으로, 시간대별로 배틀의 수가 몇 건인지 출력해주세요.
SELECT
hour,
COUNT(DISTINCT(id)) AS battle_cnt
FROM(
SELECT
*, -- 이렇게 * 뒤에 콤마 붙이면 컬럼 추가 가능
EXTRACT(HOUR FROM battle_datetime) AS hour
FROM basic.battle
)
GROUP BY
hour
ORDER BY
hour
- 질문 내용을 찾아본 결과, 스타일 차이라서 크게 문제되지 않는다고 답변받은 것을 확인!
문제 3. 각 트레이너별로 그들이 포켓몬을 포획한 첫 날(catch_date)을 찾고, 그 날짜를 'DD/MM/YYYY' 형식으로 출력해주세요. (예시: 2024-01-01 ⇒ 01/01/2024)
# 쿼리 작성하는 목표, 확인할 지표 : 요일별 배틀 횟수
# 쿼리 계산 방법 : count
# 데이터의 기간 :
# 사용할 테이블 : battle
# JOIN KEY : 없음
# 데이터의 특징 :
-- 문제2 풀 때 battle_date는 KR 기준인 거 확인했었음 (그대로 사용 가능)
SELECT
day_of_week,
count(distinct(id)) as battle_cnt
FROM(
SELECT
*,
extract(DAYOFWEEK from battle_date) AS day_of_week
FROM basic.battle
)
GROUP BY day_of_week
ORDER BY day_of_week -- 월요일부터 나오도록 정렬
/* 내 풀이 과정
SELECT
battle_dayofweek,
count(id) as battle_cnt
FROM(
SELECT
id,
battle_date,
extract(DAYOFWEEK from battle_date) AS battle_dayofweek
FROM basic.battle
)
GROUP BY
battle_dayofweek
*/
문제 5. 트레이너가 포켓몬을 처음으로 포획한 날짜와 마지막으로 포획한 날짜의 간격이 큰 순으로 정렬하는 쿼리를 작성해주세요.
# 쿼리 작성하는 목표, 확인할 지표 : 첫날과 마지막날의 간격 / 큰 순 정렬
# 쿼리 계산 방법 : (MAX-MIN) / ORDER BY DESC
# 데이터의 기간 :
# 사용할 테이블 : trainer_pokemon
# JOIN KEY : 없음
# 데이터의 특징 :
-- 문제1 풀 때 catch_date는 KR 기준이 아니니까 주의해야 함을 확인했었음
SELECT
max_catch_date,
min_catch_date,
DATETIME_DIFF(max_catch_date, min_catch_date, DAY) AS day_diff
FROM(
SELECT
trainer_id,
MAX(DATE(catch_datetime, "Asia/Seoul")) AS max_catch_date,
MIN(DATE(catch_datetime, "Asia/Seoul")) AS min_catch_date,
FROM basic.trainer_pokemon
GROUP BY
trainer_id
)
ORDER BY day_diff DESC
/* 내 풀이 과정
SELECT
max_catch_date,
min_catch_date,
DATETIME_DIFF(max_catch_date, min_catch_date, DAY) AS day_diff
FROM(
SELECT
trainer_id,
MAX(DATE(catch_datetime, "Asia/Seoul")) AS max_catch_date,
MIN(DATE(catch_datetime, "Asia/Seoul")) AS min_catch_date,
FROM basic.trainer_pokemon
GROUP BY
trainer_id
)
ORDER BY day_diff DESC
*/
