MySQL에서 dd/mm/yyyy 형식 문자열을 Unix 타임스탬프로 변환하는 방법

MySQL에서 dd/mm/yyyy 형식으로 저장된 날짜 문자열을 Unix 타임스탬프로 변환하려면 UNIX_TIMESTAMP() 함수와 STR_TO_DATE() 함수를 함께 사용하면 됩니다.

기본 문법

변환에 사용되는 기본 문법은 다음과 같습니다.

SELECT UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName,'%d/%m/%Y')) as anyVariableName FROM yourTableName;

여기서 STR_TO_DATE()가 '일/월/년' 형식의 문자열을 MySQL이 인식할 수 있는 날짜 값으로 바꾸고, UNIX_TIMESTAMP()가 해당 날짜를 Unix 타임스탬프(1970-01-01 00:00:00 UTC부터 경과한 초 단위 시간)로 변환합니다.

예제 테이블 생성

실제 동작을 확인하기 위해 먼저 테이블을 생성해 보겠습니다.

mysql> create table ConvertddmmyyyyInUnixTimeStamp
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> Created_at varchar(30),
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.57 sec)

샘플 데이터 삽입

INSERT 명령어를 사용하여 dd/mm/yyyy 형식의 날짜 문자열을 여러 개 삽입합니다.

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('10/11/2012');
Query OK, 1 row affected (0.21 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('11/12/2013');
Query OK, 1 row affected (0.23 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('10/12/2012');
Query OK, 1 row affected (0.12 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('31/01/2015');
Query OK, 1 row affected (0.19 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('24/04/2016');
Query OK, 1 row affected (0.21 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('20/09/2017');
Query OK, 1 row affected (0.26 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('15/03/2018');
Query OK, 1 row affected (0.14 sec)

저장된 데이터 조회

SELECT 문으로 테이블의 모든 레코드를 확인해 보겠습니다.

mysql> select *from ConvertddmmyyyyInUnixTimeStamp;

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

+----+------------+
| Id | Created_at |
+----+------------+
|  1 | 10/11/2012 |
|  2 | 11/12/2013 |
|  3 | 10/12/2012 |
|  4 | 31/01/2015 |
|  5 | 24/04/2016 |
|  6 | 20/09/2017 |
|  7 | 15/03/2018 |
+----+------------+
7 rows in set (0.00 sec)

Unix 타임스탬프로 변환하기

이제 앞서 소개한 문법을 적용하여 dd/mm/yyyy 형식의 문자열을 Unix 타임스탬프로 변환해 보겠습니다.

mysql> select unix_timestamp(str_to_date(Created_at,'%d/%m/%Y')) as UnixTimestamp from ConvertddmmyyyyInUnixTimeStamp;

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

+---------------+
| UnixTimestamp |
+---------------+
|    1352485800 |
|    1386700200 |
|    1355077800 |
|    1422642600 |
|    1461436200 |
|    1505845800 |
|    1521052200 |
+---------------+
7 rows in set (0.00 sec)

위 결과에서 볼 수 있듯이, 각 날짜 문자열이 성공적으로 Unix 타임스탬프 값으로 변환되었습니다. 참고로 반환되는 타임스탬프 값은 MySQL 서버의 시간대(time zone) 설정에 따라 달라질 수 있습니다.