MySQL에서 AM/PM 형식 시간 정렬하기
MySQL에서 '09:45 PM'처럼 AM/PM 형식으로 저장된 시간 데이터를 단순히 ORDER BY로 정렬하면 문자열 기준으로 정렬되어 의도한 순서대로 나오지 않습니다. 이럴 때는 ORDER BY 절과 STR_TO_DATE() 함수를 함께 사용하면 시간값을 실제 날짜·시간 타입으로 변환하여 올바르게 정렬할 수 있습니다.
기본 문법
SELECT yourColumnName FROM yourTableName
ORDER BY STR_TO_DATE(yourColumnName, '%l:%i %p');
여기서 사용된 형식 지정자는 다음과 같습니다.
- %l : 12시간 형식의 시(1~12)
- %i : 분(00~59)
- %p : AM 또는 PM
예제 테이블 생성
먼저 예제에 사용할 테이블을 생성합니다.
mysql> CREATE TABLE DemoTable (
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
UserLogoutTime VARCHAR(200)
);
Query OK, 0 rows affected (0.97 sec)
데이터 삽입
INSERT 명령어를 사용해 AM/PM 형식의 로그아웃 시간 데이터를 입력합니다.
mysql> INSERT INTO DemoTable(UserLogoutTime) VALUES('09:45 PM');
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO DemoTable(UserLogoutTime) VALUES('11:56 AM');
Query OK, 1 row affected (0.11 sec)
mysql> INSERT INTO DemoTable(UserLogoutTime) VALUES('01:01 AM');
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO DemoTable(UserLogoutTime) VALUES('02:01 PM');
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO DemoTable(UserLogoutTime) VALUES('04:10 PM');
Query OK, 1 row affected (0.15 sec)저장된 데이터 확인
SELECT 문으로 테이블의 전체 레코드를 조회해 보겠습니다.
mysql> SELECT * FROM DemoTable;
실행 결과는 다음과 같습니다.
+----+----------------+
| Id | UserLogoutTime |
+----+----------------+
| 1 | 09:45 PM |
| 2 | 11:56 AM |
| 3 | 01:01 AM |
| 4 | 02:01 PM |
| 5 | 04:10 PM |
+----+----------------+
5 rows in set (0.00 sec)
AM/PM 기준 시간 정렬 쿼리
이제 STR_TO_DATE() 함수를 활용해 시간순으로 정렬하는 쿼리를 실행합니다.
mysql> SELECT UserLogoutTime FROM DemoTable
ORDER BY STR_TO_DATE(UserLogoutTime, '%l:%i %p');
실행 결과는 다음과 같습니다.
+----------------+
| UserLogoutTime |
+----------------+
| 01:01 AM |
| 11:56 AM |
| 02:01 PM |
| 04:10 PM |
| 09:45 PM |
+----------------+
5 rows in set (0.00 sec)
정리
위 결과를 보면 오전(AM) 시간이 먼저 오름차순으로 정렬되고, 이후 오후(PM) 시간이 차례대로 정렬된 것을 확인할 수 있습니다. VARCHAR 타입으로 저장된 AM/PM 형식의 시간 데이터라도 STR_TO_DATE() 함수로 변환하면 MySQL이 이를 실제 시간값으로 인식하여 정확한 시간순 정렬이 가능합니다. 내림차순 정렬이 필요하다면 쿼리 끝에 DESC 키워드만 추가하면 됩니다.