MySQL에서 타임스탬프 기준으로 데이터 정렬하는 방법

MySQL에서 타임스탬프로 정렬하기

MySQL에서 문자열 형식으로 저장된 타임스탬프를 시간 순서대로 정렬하려면 ORDER BY 절과 STR_TO_DATE() 함수를 함께 사용해야 합니다. 기본 문법은 다음과 같습니다.

SELECT * FROM yourTableName
ORDER BY STR_TO_DATE(`yourColumnName`, '%m/%d/%Y %h:%i:%s %p');

1. 예제 테이블 생성

먼저 타임스탬프 값을 varchar 타입으로 저장하는 테이블을 만들어 보겠습니다.

mysql> CREATE TABLE DemoTable
    -> (
    -> `timestamp` varchar(100)
    -> );
Query OK, 0 rows affected (0.56 sec)

2. 샘플 데이터 삽입

INSERT 명령을 사용해 몇 개의 레코드를 추가합니다.

mysql> INSERT INTO DemoTable VALUES('06/22/2019 01:10:20 PM');
Query OK, 1 row affected (0.13 sec)

mysql> INSERT INTO DemoTable VALUES('06/22/2019 12:00:27 PM');
Query OK, 1 row affected (0.26 sec)

mysql> INSERT INTO DemoTable VALUES('06/22/2019 06:56:20 AM');
Query OK, 1 row affected (0.23 sec)

mysql> INSERT INTO DemoTable VALUES('06/22/2019 07:10:11 AM');
Query OK, 1 row affected (0.15 sec)

3. 저장된 데이터 확인

SELECT 문으로 테이블의 전체 레코드를 조회해 보겠습니다.

mysql> SELECT *FROM DemoTable;

출력 결과

+------------------------+
| timestamp              |
+------------------------+
| 06/22/2019 01:10:20 PM |
| 06/22/2019 12:00:27 PM |
| 06/22/2019 06:56:20 AM |
| 06/22/2019 07:10:11 AM |
+------------------------+
4 rows in set (0.00 sec)

출력 결과를 보면 레코드가 입력된 순서 그대로 저장되어 있으며, 시간순으로 정렬되어 있지 않습니다.

4. 타임스탬프 기준으로 정렬하는 쿼리

다음은 STR_TO_DATE() 함수를 활용해 타임스탬프를 기준으로 오름차순 정렬하는 쿼리입니다.

mysql> SELECT *FROM DemoTable ORDER BY STR_TO_DATE(`timestamp`,'%m/%d/%Y%h:%i:%s %p');

출력 결과

+------------------------+
| timestamp              |
+------------------------+
| 06/22/2019 06:56:20 AM |
| 06/22/2019 07:10:11 AM |
| 06/22/2019 12:00:27 PM |
| 06/22/2019 01:10:20 PM |
+------------------------+
4 rows in set (0.00 sec)

위 결과를 보면 오전 6시 56분부터 오후 1시 10분까지 시간 순서대로 올바르게 정렬된 것을 확인할 수 있습니다.

형식 지정자 및 참고 사항

STR_TO_DATE() 함수에 사용된 주요 형식 지정자는 다음과 같습니다.
- %m/%d/%Y : 월/일/연도 형식
- %h:%i:%s : 12시간제 시:분:초
- %p : AM/PM 표시

추가로, 컬럼이 DATETIME 또는 TIMESTAMP 타입이라면 별도의 변환 없이 ORDER BY timestamp만으로도 간단히 정렬할 수 있습니다. 내림차순 정렬이 필요한 경우 쿼리 끝에 DESC를 붙이면 됩니다.