MySQL 날짜 형식에서 숫자형 날짜 값 추출하기
'31-Jan-19'처럼 문자열로 저장된 날짜 데이터에서 숫자형(UNIX 타임스탬프) 값을 추출하려면 UNIX_TIMESTAMP() 함수와 STR_TO_DATE() 함수를 함께 사용하면 됩니다. STR_TO_DATE()는 문자열을 DATE 타입으로 변환하고, UNIX_TIMESTAMP()는 해당 날짜를 초 단위의 숫자 값으로 변환해 줍니다.
기본 문법
select UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName, "%d-%b-%y")) as anyAliasName from yourTableName;
여기서 사용된 형식 지정자의 의미는 다음과 같습니다.
- %d : 일(01~31)
- %b : 축약된 월 이름(Jan, Feb, Mar 등)
- %y : 두 자리 연도
예제 테이블 생성
먼저 예제에 사용할 테이블을 생성해 보겠습니다.
mysql> create table demo34 -> ( -> datevalue varchar(40) -> ); Query OK, 0 rows affected (1.51 sec)
데이터 삽입
insert 명령을 사용하여 테이블에 레코드를 삽입합니다.
mysql> insert into demo34 values('31-Jan-19');
Query OK, 1 row affected (0.13 sec)
mysql> insert into demo34 values('03-Mar-21');
Query OK, 1 row affected (0.12 sec)
mysql> insert into demo34 values('13-Jun-20');
Query OK, 1 row affected (0.11 sec)저장된 데이터 확인
select 문을 사용하여 테이블에 저장된 레코드를 조회합니다.
mysql> select *from demo34;
위 쿼리는 다음과 같은 결과를 출력합니다.
+-----------+ | datevalue | +-----------+ | 31-Jan-19 | | 03-Mar-21 | | 13-Jun-20 | +-----------+ 3 rows in set (0.00 sec)
숫자형 날짜 값 추출 쿼리
다음은 날짜 형식의 문자열에서 숫자형 날짜 값(UNIX 타임스탬프)을 추출하는 쿼리입니다.
mysql> select UNIX_TIMESTAMP(STR_TO_DATE(datevalue, "%d-%b-%y")) as Numeric_Date from demo34;
위 쿼리는 다음과 같은 결과를 출력합니다.
+--------------+ | Numeric_Date | +--------------+ | 1548873000 | | 1614709800 | | 1591986600 | +--------------+ 3 rows in set (0.00 sec)
실행 결과를 보면 '31-Jan-19'는 1548873000으로, '03-Mar-21'은 1614709800으로, '13-Jun-20'은 1591986600으로 각각 변환된 것을 확인할 수 있습니다. 이렇게 얻은 UNIX 타임스탬프 값은 필요할 때 FROM_UNIXTIME() 함수를 사용하여 다시 사람이 읽기 쉬운 날짜 형식으로 되돌릴 수도 있습니다.