MySQLでAM/PM形式の時刻を正しく並べ替える方法

はじめに

MySQLで「09:45 PM」のようなAM/PM形式の時刻文字列をVARCHAR型で保存している場合、単純にORDER BYを使うと文字列として比較されるため、正しい時刻順に並び替えられません。そこで役立つのがSTR_TO_DATE()関数です。この関数で時刻文字列を日付・時刻型の値に変換してからソートすることで、正確な時刻順に並べ替えることができます。

基本構文

SELECT yourColumnName FROM yourTableName
ORDER BY STR_TO_DATE(yourColumnName, '%l:%i %p');

使用しているフォーマット指定子の意味は以下の通りです。

• %l：12時間制の「時」（0埋めなし）
• %i：「分」（0埋めあり）
• %p：AM または PM

実際の例で確認しよう

1. テーブルの作成

まず、サンプル用のテーブルを作成します。

mysql> CREATE TABLE DemoTable (
    Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    UserLogoutTime VARCHAR(200)
);
Query OK, 0 rows affected (0.97 sec)

2. データの挿入

INSERTコマンドでレコードを挿入します。

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)

3. 登録データの確認

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()とORDER BYを組み合わせることで、時刻順に並べ替えられます。

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）の時刻が時間順に正しく並んでいることがわかります。

補足：降順で並べ替えたい場合

逆に、遅い時刻から順に表示したい場合はDESCを追加するだけです。

SELECT UserLogoutTime FROM DemoTable
ORDER BY STR_TO_DATE(UserLogoutTime, '%l:%i %p') DESC;

まとめ

VARCHAR型で保存されたAM/PM形式の時刻をソートするには、ORDER BYとSTR_TO_DATE()を組み合わせるのが最も手軽な方法です。ただし、ソートや検索を頻繁に行うカラムであれば、データ型をTIME型に変更するか、生成カラム（GENERATED COLUMN）にインデックスを作成しておくと、パフォーマンス面でより有利になります。