MySQLで文字列（varchar）をタイムスタンプ形式に変換する方法

MySQLで文字列をタイムスタンプ形式に変換するには？

MySQLで文字列（varchar型）をタイムスタンプ形式に変換するには、STR_TO_DATE()関数とDATE_FORMAT()関数を組み合わせて使用します。STR_TO_DATE()で文字列を日付値に解析し、DATE_FORMAT()で目的の形式に整形します。

まず、実際の動作を確認するためのサンプルテーブルを作成しましょう。

mysql> create table DemoTable1602
    -> (
    -> ReportingDate varchar(40)
    -> );
Query OK, 0 rows affected (0.51 sec)

テーブルへのデータ挿入

次に、INSERTコマンドを使ってテーブルにレコードを挿入します。ここでは「Wed Oct 02 16:10:45 IST 2019」のような形式の文字列を扱います。

mysql> insert into DemoTable1602 values('Wed Oct 02 16:10:45 IST 2019');
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1602 values('Fri May 31 13:00:10 IST 2019');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable1602 values('Mon Dec 31 14:20:00 IST 2018');
Query OK, 1 row affected (0.23 sec)

挿入したデータの確認

SELECT文を使って、テーブル内のすべてのレコードを表示します。

mysql> select * from DemoTable1602;

上記のクエリを実行すると、次のような出力が得られます。

+------------------------------+
| ReportingDate                |
+------------------------------+
| Wed Oct 02 16:10:45 IST 2019 |
| Fri May 31 13:00:10 IST 2019 |
| Mon Dec 31 14:20:00 IST 2018 |
+------------------------------+
3 rows in set (0.00 sec)

文字列をタイムスタンプ形式に変換するクエリ

以下が、MySQLで文字列をタイムスタンプ形式に変換するクエリです。フォーマット指定子%a %b %d %T IST %Yを使って元の文字列を解析し、%d-%m-%Y %H:%i:%sの形式に変換しています。

mysql> select date_format(str_to_date(ReportingDate,'%a %b %d %T IST %Y'),'%d-%m-%Y %H:%i:%s') from DemoTable1602;

実行結果は以下の通りです。文字列が「日-月-年 時:分:秒」の形式に変換されていることが確認できます。

+----------------------------------------------------------------------------------+
| date_format(str_to_date(ReportingDate,'%a %b %d %T IST %Y'),'%d-%m-%Y %H:%i:%s') |
+----------------------------------------------------------------------------------+
| 02-10-2019 16:10:45                                                              |
| 31-05-2019 13:00:10                                                              |
| 31-12-2018 14:20:00                                                              |
+----------------------------------------------------------------------------------+
3 rows in set (0.03 sec)

使用したフォーマット指定子の解説

STR_TO_DATE()で使用した主なフォーマット指定子は以下の通りです。

• %a：曜日の省略形（例：Wed、Fri）
• %b：月名の省略形（例：Oct、May）
• %d：日（01〜31）
• %T：24時間形式の時刻（hh:mm:ss）
• %Y：4桁の年

このように、STR_TO_DATE()とDATE_FORMAT()を組み合わせることで、さまざまな形式の文字列を自由にタイムスタンプ形式へ変換できます。データの形式に合わせてフォーマット指定子を調整してください。