MySQLでvarchar型の時刻データを正しい時間形式に変換する方法

varchar型として保存された「1620」や「2345」のような時刻データを、実際の時間形式（例：04:20）に変換したい場合、MySQLのTIME_FORMAT()関数を使用することで実現できます。

この記事では、SUBSTRING()とCONCAT()を組み合わせて、4桁の文字列を「時：分」の形式に整形する手順を解説します。

1. サンプルテーブルの作成

まず、到着時刻をvarchar型で格納するテーブルを作成します。

mysql> create table DemoTable1591
    -> (
    -> ArrivalTime varchar(20)
    -> );
Query OK, 0 rows affected (0.58 sec)

2. レコードの挿入

INSERTコマンドを使って、いくつかの時刻データを挿入します。

mysql> insert into DemoTable1591 values('1620');
Query OK, 1 row affected (0.09 sec)
mysql> insert into DemoTable1591 values('2345');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable1591 values('2210');
Query OK, 1 row affected (0.12 sec)

3. テーブルの内容を確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> select * from DemoTable1591;

以下の出力が得られます。

+-------------+
| ArrivalTime |
+-------------+
| 1620        |
| 2345        |
| 2210        |
+-------------+
3 rows in set (0.00 sec)

4. TIME_FORMAT()で時刻形式に変換

ここがポイントです。SUBSTRING()で先頭2文字（時）と残りの文字（分）を取り出し、CONCAT()でコロン「:」を挟んで連結した上で、TIME_FORMAT()に渡します。

mysql> select TIME_FORMAT(CONCAT(SUBSTRING(ArrivalTime, 1,2), ':', SUBSTRING(ArrivalTime, 3,4)), '%h:%i') from DemoTable1591;

実行結果は以下のとおりです。

+---------------------------------------------------------------------------------------------+
| TIME_FORMAT(CONCAT(SUBSTRING(ArrivalTime, 1,2), ':', SUBSTRING(ArrivalTime, 3,4)), '%h:%i') |
+---------------------------------------------------------------------------------------------+
| 04:20                                                                                       |
| 11:45                                                                                       |
| 10:10                                                                                       |
+---------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

処理の流れの解説

• SUBSTRING(ArrivalTime, 1, 2)：文字列の1文字目から2文字分を抽出し、「時」の部分を取得します（例：「1620」→「16」）。
• SUBSTRING(ArrivalTime, 3, 4)：3文字目以降を抽出し、「分」の部分を取得します（例：「1620」→「20」）。
• CONCAT()：「16」「:」「20」を連結して「16:20」という時刻形式の文字列を作ります。
• TIME_FORMAT(..., '%h:%i')：%hは12時間表記の時、%iは分を意味します。そのため「1620」は「04:20」、「2345」は「11:45」、「2210」は「10:10」と表示されます。

補足：24時間表記で表示したい場合

24時間表記で出力したい場合は、フォーマット指定子を%H:%iに変更してください。

mysql> select TIME_FORMAT(CONCAT(SUBSTRING(ArrivalTime, 1,2), ':', SUBSTRING(ArrivalTime, 3,4)), '%H:%i') from DemoTable1591;

この場合、「1620」は「16:20」、「2345」は「23:45」と表示され、元のデータが持つ時刻情報がそのまま反映されます。

このように、TIME_FORMAT()と文字列関数を組み合わせれば、varchar型で保存された時刻データもSQLクエリだけで簡単に整形できます。なお、頻繁に時刻計算を行う場合は、カラムの型をTIME型に変更しておくことをおすすめします。