사용자 행동 데이터를 분석하다보면 이벤트 간 발생 시각의 차이를 초 단위로 봐야 하는 경우가 많습니다. 페이지에 진입해서, 스크롤을 내리고, 요소를 클릭하는 행위들이 짧은 시간 안에 발생하기 때문입니다. 이 글에서는 SQLite, MySQL, PostgreSQL에서 두 타임스탬프 간 차이를 초 단위로 계산하는 방법을 알아보겠습니다.앞으로 나오는 <code>sample</code> 이라는 데이터는 이 WITH 문으로 정의한 CTE 입니다. 사용자 ‘AMtJS3o4Ca’의 페이지뷰, 스크롤 로그 사이에 얼마나 시간이 걸렸는지 계산하기 위해 정의한 CTE로, 이벤트 발생 시각(<code>event_timestamp_kst</code>)과 다음 이벤트 발생 시각(<code>after_event_at</code>) 정보를 담고 있습니다.<pre><code class="language-sql">WITH sample AS (
 SELECT event_name
 , event_timestamp_kst -- 이벤트 발생 시각
 , LEAD(event_timestamp_kst) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS after_event_at -- 다음 이벤트 발생 시각
 FROM ga
 WHERE user_pseudo_id = &#039;AMtJS3o4Ca&#039;
 AND event_name IN (&#039;page_view&#039;, &#039;scroll&#039;)
)</code></pre><div data-type="callout" data-emoji="💡">solvesql의 <a href="https://solvesql.com/playground/" target="_blank">플레이그라운드</a>에서 SQLite 코드를 실행해 볼 수 있습니다. 데이터베이스로 ‘데이터리안 블로그 GA 로그 (2022년 1월)’을 선택하고 코드를 실행해 보세요. 데이터리안의 <a href="https://datarian.io/bootcamp/sql-package" target="_blank">SQL 캠프 입문반과 실전반</a> 수강생은 MySQL을, <a href="https://datarian.io/bootcamp/sql-master" target="_blank">SQL 캠프 마스터반</a> 수강생은 PostgreSQL을 추가로 사용할 수 있습니다.</div><h1 id="1af9463d984d8114b20fd4fa11d21a79">SQLite</h1><h2 id="1b59463d984d808596bfdbbea15e11d3"><code>STRFTIME()</code> 함수를 이용한 풀이</h2>SQLite에서는 <code>STRFTIME()</code> 함수를 이용해 두 타임스탬프간 차이를 계산할 수 있습니다. 이 함수는 날짜, 시간을 특정 시간 형식으로 변환하는데, 첫 번째 인자로 ‘%s’를 받는 경우 유닉스(Unix) 시간으로 변환합니다. 유닉스 시간이란, 1970-01-01 00:00:00 UTC 이후 경과한 시간을 의미합니다. 예를 들어, 아래 코드의 결과는 1741776541이며, 이는 2025년 3월 12일 10시 49분 1초가 기준 시간으로부터 1741776521초 경과했다는 것을 의미합니다.<pre><code class="language-sql">SELECT STRFTIME(&#039;%s&#039;, &#039;2025-03-12 10:49:01&#039;)</code></pre>이를 이용해 아래와 같이 코드를 작성하면 두 타임스탬프간의 차이를 초 단위로 계산할 수 있습니다.<pre><code class="language-sql">SELECT event_timestamp_kst
 , after_event_at
 , STRFTIME(&#039;%s&#039;, after_event_at) - STRFTIME(&#039;%s&#039;, event_timestamp_kst) AS timestamp_diff
FROM sample</code></pre><code>STRFTIME()</code> 함수는 ‘%s’ 이외에도 여러가지 형식 코드를 받아 날짜 시간 데이터를 변환할 수 있습니다. 아래 표를 참고해주세요.<table><tbody><tr><th>형식 코드</th><th>설명</th></tr><tr><td><code>%Y</code></td><td>연도 (4자리)</td></tr><tr><td><code>%y</code></td><td>연도 (2자리)</td></tr><tr><td><code>%m</code></td><td>월 (01~12)</td></tr><tr><td><code>%d</code></td><td>일 (01~31)</td></tr><tr><td><code>%H</code></td><td>시간 (00~23)</td></tr><tr><td><code>%M</code></td><td>분 (00~59)</td></tr><tr><td><code>%S</code></td><td>초 (00~59)</td></tr><tr><td><code>%s</code></td><td>Unix 시간 (초 단위)</td></tr></tbody></table><h2 id="1b49463d984d80baa0a6f5f8c7af57d1"><code>JULIANDAY()</code> 함수를 이용한 풀이</h2><code>JULIANDAY()</code> 함수를 사용한 풀이도 많이 소개되지만, 이 함수는 일 단위 미만은 소수점으로 표기하여 <a href="https://ko.wikipedia.org/wiki/%EB%B6%80%EB%8F%99%EC%86%8C%EC%88%98%EC%A0%90" target="_blank">부동소수점</a> 연산 오차가 생길 수 있습니다. 따라서 특별한 이유 아니라면 지양하는 방법입니다.Julian Day는 Joseph Scaliger가 도입한 시간 계산 시스템으로, 기원전 4713년 1월 1일 정오(UTC)부터 연속적으로 일수를 셉니다. 주로 천문학자들이 사용하는 방식이며 일수는 정수로 표현하고, 그 이하의 단위는 소수점으로 표기합니다.예를 들어, 아래와 같이 쿼리를 작성하면 하단 테이블에 있는 결과를 얻을 수 있습니다.<pre><code class="language-sql">SELECT event_timestamp_kst
 , JULIANDAY(event_timestamp_kst)
FROM ga
LIMIT 1</code></pre><table><tbody><tr><th>event_timestamp_kst</th><th>julianday_event_timestamp_kst</th></tr><tr><td>2022-01-20 07:30:28</td><td>2459599.81282407</td></tr></tbody></table>이런 <code>JULIANDAY()</code> 함수의 특성을 이용해 아래와 같이 두 타임스탬프의 차이를 계산할 수 있습니다. 결과물이 일 단위로 나오기 때문에, 초 단위로 변환하기 위해 24시간, 60분, 60초를 곱해주어야 합니다. 반올림을 하기 전 결과는 14.000000453 으로, 부동소수점 연산 오차가 있습니다. <code>ROUND()</code> 함수로 결과물을 반올림해주어 연산 오차를 제거합니다.<pre><code class="language-sql">SELECT event_timestamp_kst
 , after_event_at
 , ROUND((JULIANDAY(after_event_at) - JULIANDAY(event_timestamp_kst)) * 24 * 60 * 60) AS timestamp_diff
FROM sample</code></pre><h1 id="1af9463d984d80d7887de33f1b10999b">MySQL</h1>MySQL에서는 <code>TIMESTAMPDIFF()</code> 함수를 이용해 두 타임스탬프 간의 차이를 초 단위로 계산할 수 있습니다.<pre><code class="language-sql">SELECT TIMESTAMPDIFF(SECOND, event_timestamp_kst, after_event_at) AS timestamp_diff
FROM sample</code></pre><code>TIMESTAMPDIFF()</code> 함수는 <code>SECOND</code> 이외에도 다양한 인자를 받아 두 타임스탬프 간 차이를 원하는 형태로 가공해줍니다. 아래 표를 참고해주세요.<table><tbody><tr><th>코드</th><th>설명</th></tr><tr><td>SECOND</td><td>초</td></tr><tr><td>MINUTE</td><td>분</td></tr><tr><td>HOUR</td><td>시간</td></tr><tr><td>DAY</td><td>일</td></tr><tr><td>WEEK</td><td>주</td></tr><tr><td>MONTH</td><td>월</td></tr><tr><td>QUARTER</td><td>분기</td></tr><tr><td>YEAR</td><td>연도</td></tr></tbody></table><h1 id="1af9463d984d804dbf65c78d4fd73291">PostgreSQL</h1>PostgreSQL에서는 두 타임스탬프를 빼면 interval 타입의 결과를 반환합니다. 예를 들어, 아래 코드는 interval 타입의 ‘0 year 0 month 0 day 0 hour 0 minute 14 seconds’를 반환합니다.<pre><code class="language-sql">SELECT after_event_at - event_timestamp_kst
FROM sample</code></pre>이 값을 초 단위로 변환하는데는 <code>EXTRACT(EPOCH FROM … )</code> 함수가 사용됩니다. <code>EXTRACT(EPOCH FROM … )</code>은 타임스탬프에 적용시 1970년 1월 1일 00:00:00 UTC 이후 경과한 초수를 연산해주고, interval 타입의 데이터에 적용시 초 단위로 변환해줍니다. 아래와 같이 작성시 ‘0 year 0 month 0 day 0 hour 0 minute 14 seconds’를 초 단위로 변환하여 14라는 결과를 출력합니다.<pre><code class="language-sql">SELECT EXTRACT(EPOCH FROM after_event_at - event_timestamp_kst) AS timestamp_diff
FROM sample</code></pre><code>EXTRACT()</code> 함수는 <code>EPOCH</code> 이외에도 다양한 인자를 받아 타임스탬프 또는 interval 타입의 데이터를 가공할 수 있습니다. <a href="https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" target="_blank">PostgreSQL 공식 문서</a>를 참고해주세요.<table><tbody><tr><th>코드</th><th>설명</th></tr><tr><td>CENTURY</td><td>세기</td></tr><tr><td>DAY</td><td>일 (1~31)</td></tr><tr><td>DOW</td><td>Day of Week, 주의 일 (일요일=0, …, 토요일=6)</td></tr><tr><td>DOY</td><td>Day of Year, 연도의 일 (1~365/366)</td></tr><tr><td>…</td><td>…</td></tr></tbody></table><h1 id="1b69463d984d802097e1f5f8d3dd7c15">주의할 점</h1>MySQL과 다른 DBMS를 함께 사용하다보면 특히 이 날짜 계산하는 부분을 자주 헷갈리는데요. MySQL은 <code>TIMESTAMPDIFF(SECOND, a, b)</code> 라고 적었다면 연산은 <code>b - a</code>를 하는데, 다른 DBMS, 예를 들어 PostgreSQL은 <code>EXTRACT(EPOCH FROM b - a)</code> 라면 서술된 순서대로 <code>b - a</code> 연산을 하기 때문입니다. 이 점을 잘 기억해두면 실수를 줄일 수 있습니다.<h1 id="1af9463d984d809a8347c5170c46dcdb">요약</h1><table><tbody><tr><th>DBMS</th><th>사용 함수</th><th>코드 예시</th></tr><tr><td>SQLite</td><td>- <code>STRFTIME()</code> - <code>JULIANDAY()</code></td><td>- <code>STRFTIME(&#039;%s&#039;, after_event_at) - STRFTIME(&#039;%s&#039;, event_timestamp_kst)</code> - <code>ROUND((JULIANDAY(after_event_at) - JULIANDAY(event_timestamp_kst)) * 24 * 60 * 60)</code></td></tr><tr><td>MySQL</td><td><code>TIMESTAMPDIFF()</code></td><td><code>TIMESTAMPDIFF(SECOND, event_timestamp_kst, after_event_at)</code></td></tr><tr><td>PostgreSQL</td><td><code>EXTRACT(EPOCH FROM … )</code></td><td><code>EXTRACT(EPOCH FROM after_event_at - event_timestamp_kst)</code></td></tr></tbody></table><h1 id="1af9463d984d80e584d7d29d3efaebe1">연습 문제</h1><a href="https://solvesql.com/problems/redefine-session-2/" target="_blank">세션 유지 시간을 10분으로 재정의하기</a> 문제를 풀고 배운 내용을 복습해보세요! <a href="https://solvesql.com/collections/advent-of-sql-2024/" target="_blank">Advent of SQL</a>에서 더 많은 SQL 문제를 풀어볼 수 있습니다.

사용자 행동 데이터를 분석하다보면, 이벤트 간 발생 시각의 차이를 초 단위로 봐야하는 경우가 많습니다. 이 글에서는 SQLite, MySQL, PostgreSQL 세가지 DBMS에서 두 타임스탬프 간 차이를 초 단위로 계산하는 방법을 알아보겠습니다.

두 타임스탬프 간 차이를 초 단위로 계산하기 [SQLite, MySQL, PostgreSQL]

Contents

데이터 분석 소식을
매월 받아보세요

함께 읽어보면 좋은 글

26개의 재귀적 GPT 프롬프트로 SQL 분석가 대체하기

실무에서 자주 사용하는 분석 프레임워크 5가지

데이터 분석가 되려면 SQL, Python, R 다 배워야 하나요?

데이터 분석 소식을
매월 받아보세요데이터 분석 소식을 받아보세요