[SQL로 시작하는 데이터 분석] (3) - 시계열 분석

이제부터 본격적으로 데이터분석을 시작해 볼 것이다 !  이번 주차는 시계열 분석으로 진행해보겠다. 내용이 많아서 아마 포스팅을 두개로 나눌 듯 싶다. 

 

먼저, 시계열 분석이란?

➡️ 시계열이란 시간 순으로 재정렬된 데이터의 배열을 뜻한다. 

      시계열 분석이란 시간의 흐름에 따라 수집된 시계열 데이터를 분석하여 그 안에 숨겨진 패턴, 트렌드, 계절성, 순환성 등을 파악하고 미래 값을 예측하거나 의사결정을 내리는 기법이다. 

 

1. 시계열 데이터의 날짜 및 시간 데이터 조작

 일반적인 분석이라면 단순히 특정 이벤트 이후로 며칠 혹은 몇 개월이 지났는지 계산하겠지만, 시계열 분석에서는 시간에 따른 트렌드를 분석하는 것이 주 목적이다. 이번 포스팅에서는 인터벌 개념을 이용한 날짜 계산과 시간조작 방법에 대해 알아 볼 것이다.

 

** 여기서 인터벌이란 ? : 월,일,시, 분 등의 단위로 시간의 폭을 나타내는 데이터 타입, : 데이터베이스에 저장은 가능하나, 실질적으로 저장하는 일은 드뭄

 

1) 시간대 변환 

시간대 변환을 이해하기에 앞서, 먼저 시간대에 대한 기본 개념과 관련된 배경지식을 알아두는 것이 좋다.

🕐 시간대란?

지구는 하루에 한 바퀴, 약 24시간에 걸쳐 자전한다. 이 기준에 따라 전 세계는 지역마다 서로 다른 표준시(standard time), 즉 시간대(time zone)를 사용하고 있다. 예를 들어, 한국은 KST (Korea Standard Time)로, 협정 세계시(UTC)보다 9시간 빠른 UTC+9를 기준으로 한다.

☀️ 일광 절약 시간제(Daylight Saving Time, DST)

일부 국가는 여름철 낮 시간이 길어지는 것을 활용해, **시계를 1시간 앞당기는 일광 절약 시간제(DST)**를 도입한다. 이는 주로 위도가 높은 지역에서 에너지 소비 절감이나 생활 편의 증대를 위해 시행된다.

대표적으로 미국, 유럽, 호주 등이 DST를 사용하며, 한국, 일본, 중국과 같은 동아시아 국가에서는 사용하지 않는다.

예를 들어, 미국 캘리포니아는 겨울에는 PST (Pacific Standard Time, UTC-8)를 사용하고, 여름에는 PDT (Pacific Daylight Time, UTC-7)로 변경된다. 이처럼 시간대는 계절에 따라 달라질 수 있으며, 이로 인해 시간대 변환이 복잡해질 수 있다.

⏳ 시간대 축약어(Time Zone Abbreviations)

시간대는 보통 약어로 표현된다. 아래는 자주 사용되는 시간대 약어들이다.

• PST: Pacific Standard Time (UTC-8)
• PDT: Pacific Daylight Time (UTC-7)
• EST: Eastern Standard Time (UTC-5)
• EDT: Eastern Daylight Time (UTC-4)

시간대 축약어는 지역과 계절에 따라 달라질 수 있기 때문에, 데이터 분석이나 일정 조율 시 주의가 필요하다.

 

 

2) DB와 시간대

시간대 변환은 데이터베이스(DB)에서도 중요한 이슈 중 하나다. 특히 전 세계 사용자 데이터를 다룰 때는 타임스탬프(Timestamp)의 저장 방식과 시간대 해석이 분석의 정확도에 큰 영향을 미친다.

📌 DB에서의 타임스탬프 저장 방식

• 대부분의 데이터베이스는 타임스탬프를 YYYY-MM-DD hh:mi:ss 형식으로 저장한다.
• 과거 오래된 시스템이나 DB에서는 그리니치 평균시(GMT)를 기준으로 저장되기도 했지만, 현재는 대부분 협정 세계시(UTC)**를 사용한다.

UTC는 일광 절약 시간제(DST)의 영향을 받지 않기 때문에 1년 내내 일정한 기준 시간을 유지할 수 있어 분석에 유리하다.

⚠️ UTC의 한계

UTC로 저장된 타임스탬프는 이벤트가 실제로 발생한 로컬 시간(local time)을 직접적으로 알 수 없다는 단점이 있다.

예를 들어, 전 세계에 사용자가 있는 앱의 로그인 기록을 분석하려고 할 때, DB에 저장된 UTC 타임스탬프를 각 사용자의 지역 시간으로 변환하지 않으면 정확한 사용 행태를 파악하기 어렵다.

 

3) 시간대 변환 방법 

✅ 기본 개념

• 모든 로컬 시간대는 UTC를 기준으로 한 오프셋(offset) 값을 갖는다.
  • 예: PDT는 UTC-7, PST는 UTC-8
• 타임스탬프에 시간대 정보가 포함되어 있는 경우, 오프셋 값을 기반으로 다른 시간대로 변환할 수 있다.

 

🛠️ AT TIME ZONE 키워드 (PostgreSQL 등)

-- UTC로 저장된 타임스탬프를 PST로 변환
SELECT '2020-09-01 00:00:00 -0' AT TIME ZONE 'PST';

-- 결과
timezone
---------------------
2020-08-31 16:00:00

 

• AT TIME ZONE은 특정 문자열(예: 'PST')을 넣거나, 동적으로 DB 필드를 사용할 수도 있다.
• 시간대가 바뀌는 계절(DST 적용 시기)에도 자동으로 오프셋이 반영된다.

 

🛠️ convert_timezone / convert_tz 함수 (Redshift, MySQL 등)

-- Redshift 예시
SELECT convert_timezone('PST','2020-09-01 00:00:00 -0');

-- 결과
timezone
---------------------
2020-08-31 16:00:00

 

DBMS 종류에 따라 함수명이 다르며, 시간대 문자열 입력 형식도 다를 수 있으므로 확인이 필요하다.

+) 데이터베이스에 따라 시간대 정보 시스템 테이블 쿼리가 다르니 아래 표를 참고!! 

데이터베이스	시스템 테이블
Postgres	pg_timezone_names
MySQL	mysql.time_zone_names
SQL Server	sys.time_zone_info
Redshift	pg_timezone_names

 

 

4) 날짜 및 타임스탬프 형식 변환  

데이터베이스에서 날짜와 시간은 분석, 로깅, 스케줄링 등 다양한 작업에서 핵심적인 요소다. 특히 데이터가 저장되는 기준, 시스템 시간 기반 함수들, 그리고 날짜/시간을 가공하는 함수들에 대한 이해는 필수적이다.

 

🕒 시스템 시간 기준

데이터베이스에서 current_timestamp, now() 등 시간 관련 함수들은 DB가 동작하는 서버의 시스템 시간을 기준으로 동작한다. 따라서 동일한 쿼리라도 서버의 시간대 설정에 따라 다르게 반환될 수 있음에 주의해야 한다.

 

5) 시간 관련 주요 SQL 함수 정리

  ✅ 현재 날짜 및 시간 반환 함수

SELECT current_date;         -- 현재 날짜
SELECT current_timestamp;    -- 현재 날짜 + 시간 (타임스탬프)
SELECT localtimestamp;       -- 로컬 타임스탬프
SELECT now();                -- 현재 타임스탬프
SELECT current_time;         -- 현재 시간
SELECT localtime;            -- 로컬 시간
SELECT timeofday();          -- 텍스트 형태로 현재 시간 반환

now()나 get_date()처럼 괄호가 있는 함수는 인자를 넣지 않아도 되지만 괄호는 생략하지 않는 것이 원칙이다.

 

 

✂️ 특정 단위로 자르는 함수: date_trunc

특정 시점에서 년/월/일 단위로 잘라 분석할 때 유용하다.

-- PostgreSQL 예시
SELECT date_trunc('month', '2020-10-04 12:33:55'::timestamp);

-- 결과
2020-10-01 00:00:00

‘월’ 단위로 자르면 일, 시, 분, 초가 초기화된다.

 

 

🧩 date_format (MySQL 등)

date_trunc 함수를 지원하지 않는 DB에서는 date_format으로 유사한 결과를 얻을 수 있다.

SELECT date_format('2020-10-04 12:33:55', '%Y-%m-01') AS date_trunc;

-- 결과
2020-10-01 00:00:00

 

 

🔍 원하는 구성 요소만 추출: date_part, extract

타임스탬프에서 연도, 월, 일, 시간 등 필요한 정보만 분리해서 추출할 수 있다.

-- date_part 함수
SELECT date_part('year', current_timestamp);
SELECT date_part('month', current_timestamp);
SELECT date_part('day', current_timestamp);

-- extract 함수
SELECT extract('hour' FROM current_timestamp);

반환값은 **float(실수형)**으로 나오며, 필요 시 정수형으로 변환해 사용할 수 있다.

 

 

⏳ interval과 함께 사용하기

interval은 시간 간격을 의미하며, extract, date_part 함수와 함께 사용할 수 있다

-- 30일 간격에서 ‘일’ 추출
SELECT date_part('day', interval '30 days');
SELECT extract('day' FROM interval '30 days');  -- 결과: 30.0

-- 단위가 맞지 않으면 0 반환
SELECT extract('day' FROM interval '3 months'); -- 결과: 0.0

interval을 사용할 때는 단위가 일치하는지 확인해야 원하는 값을 얻을 수 있다.

 

 

🗣️ 날짜나 시간 이름 가져오기: to_char

날짜/시간에서 요일, 월 이름 등 텍스트 데이터를 추출하고 싶을 때 사용한다.

SELECT to_char(current_timestamp, 'Day');    -- Monday
SELECT to_char(current_timestamp, 'Month');  -- July

 

6) 유용한 날짜 조작 팁

✅ 유닉스 시간 → 타임스탬프 변환

SELECT to_timestamp(1720000000);

 

✅ 날짜 + 시간 이어 붙이기

SELECT date '2020-09-01' + time '03:00:00' AS timestamp;
-- 결과: 2020-09-01 03:00:00

 

✅ 날짜 생성 함수

• make_date (PostgreSQL)

SELECT make_date(2020, 09, 01); -- 결과: 2020-09-01

• date_from_parts (SQL Server)

SELECT date_from_parts(2020, 09, 01);

 

 

✅ 문자열로 이어 붙여 날짜 만들기

SELECT to_date(concat(2020, '-', 09, '-', 01), 'YYYY-MM-DD') AS date;

문자열을 조합한 후 to_date로 캐스팅하여 날짜 타입으로 변환할 수 있다.

 

 

 

---

지금까지 정리된 내용은 이해 중심이고, 실전에서는 활용을 할 수 있어야되는데,,,, 잘 할 수 있을지 모르겠다. 일단 시계열 전체적인 챕터를 리뷰하고 이후에 쿼리 템플릿이나 직접 작은 분석들을 실행해보는 것이 좋을 것 같다.

 

마무리하자면, 이번 포스팅에서는 시계열 데이터 분석을 위해 날짜/시간, 시간대, 그리고 그외에 관련된 SQL함수들을 정리해봤다. 다음포스팅에는 날짜 계산, 시간 계산, 서로다른 출처의 날짜합치기 등등을 할 예정이다!! 후욱후욱 이걸 어케 기억하지 싶긴한데,,, 차암.... 익숙해지는날이 오겠지! 

 

오늘도 아자잣 다들 홧팅이ㄷ ㅏ !