MySQL에서 Unix 타임스탬프를 날짜 형식으로 변환하는 방법

MySQL에 저장된 Unix 타임스탬프(bigint) 값을 사람이 읽기 쉬운 날짜·시간 형식으로 바꾸려면 from_unixtime() 함수와 date_format() 함수를 조합하면 됩니다. 기본 구문은 다음과 같습니다.

select date_format(from_unixtime(yourColumnName), '%b %d, %Y %l:%i %p PDT') from yourTableName;

주요 함수 및 형식 지정자

from_unixtime() — 초 단위 Unix 타임스탬프를 DATETIME 값으로 변환합니다.
date_format() — 날짜 값을 지정한 패턴에 맞춰 문자열로 포맷합니다.
%b — 축약된 월 이름(Jan ~ Dec)
%d — 두 자리 일(01 ~ 31)
%Y — 네 자리 연도(예: 2019)
%l — 12시간제 시간(앞자리 0 없음)
%i — 분(00 ~ 59)
%p — 오전/오후 표시(AM 또는 PM)

1. 예제 테이블 생성

위 구문을 실제로 확인하기 위해 테이블을 먼저 만들어 보겠습니다. 테이블 생성 쿼리는 다음과 같습니다.

mysql> create table unixTimeStampFormatDemo
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> MyTimeStampValue bigint
    -> );
Query OK, 0 rows affected (0.59 sec)

2. 샘플 데이터 삽입

insert 명령으로 몇 개의 Unix 타임스탬프 값을 입력합니다.

mysql> insert into unixTimeStampFormatDemo(MyTimeStampValue) values(1334428200);
Query OK, 1 row affected (0.20 sec)
mysql> insert into unixTimeStampFormatDemo(MyTimeStampValue) values(1513881000);
Query OK, 1 row affected (0.15 sec)
mysql> insert into unixTimeStampFormatDemo(MyTimeStampValue) values(1542738600);
Query OK, 1 row affected (0.14 sec)
mysql> insert into unixTimeStampFormatDemo(MyTimeStampValue) values(1551637800);
Query OK, 1 row affected (0.15 sec)

3. 저장된 데이터 확인

select 문으로 테이블의 전체 레코드를 조회합니다.

mysql> select *from unixTimeStampFormatDemo;

실행 결과는 다음과 같습니다.

+----+------------------+
| Id | MyTimeStampValue |
+----+------------------+
|  1 | 1334428200       |
|  2 | 1513881000       |
|  3 | 1542738600       |
|  4 | 1551637800       |
+----+------------------+
4 rows in set (0.00 sec)

4. Unix 타임스탬프를 날짜 형식으로 변환

이제 from_unixtime()과 date_format()을 사용해 Unix 타임스탬프를 읽기 쉬운 datetime 형식으로 변환해 보겠습니다.

mysql> select date_format(from_unixtime(MyTimeStampValue), '%b %d, %Y %l:%i %p PDT') from unixTimeStampFormatDemo;

변환 결과는 다음과 같습니다.

+-----------------------------------------------------------------------+
| date_format(from_unixtime(MyTimeStampValue), '%b %d, %Y %l:%i %p PDT') |
+-----------------------------------------------------------------------+
| Apr 15, 2012 12:00 AM PDT                                             |
| Dec 22, 2017 12:00 AM PDT                                             |
| Nov 21, 2018 12:00 AM PDT                                             |
| Mar 04, 2019 12:00 AM PDT                                             |
+-----------------------------------------------------------------------+
4 rows in set (0.00 sec)

참고 사항

from_unixtime()은 MySQL 서버의 타임존(time_zone) 설정을 따르므로, 환경에 따라 결과 시간이 다르게 표시될 수 있습니다. 특정 시간대(PDT 등)를 명확하게 반영하고 싶다면 convert_tz() 함수를 함께 사용하거나 세션의 time_zone 변수를 조정하면 됩니다. 또한 밀리초 단위 타임스탬프라면 from_unixtime(컬럼명/1000)처럼 값을 나눈 뒤 변환해야 합니다.