MySQLで日付形式を「yyyy-mm-ddThh:mm:ss.sssZ」から「yyyy-mm-dd hh:mm:ss」に変換する方法

MySQLでは、ISO 8601形式（yyyy-mm-ddThh:mm:ss.sssZ）で保存された日付データを、一般的なyyyy-mm-dd hh:mm:ss形式に変換できます。この変換には、STR_TO_DATE()関数とDATE_FORMAT()関数を組み合わせて使用します。

本記事では、実際のテーブルを作成し、日付形式を変換する手順を具体的なSQL文とともに解説します。

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

まず、日付を文字列型（VARCHAR）で保存するテーブルを作成します。

mysql> create table DemoTable668(JoiningDate varchar(200));
Query OK, 0 rows affected (0.97 sec)

2. テストデータの挿入

次に、INSERTコマンドを使ってレコードを挿入します。ここでは、日付をyyyy-mm-ddThh:mm:ss.sssZ形式（末尾に「T」と「Z」が付くISO 8601形式）で登録しています。

mysql> insert into DemoTable668 values('2001-01-10T06:20:00.000Z');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable668 values('2019-07-20T04:00:00.000Z');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable668 values('2016-02-12T05:10:50.000Z');
Query OK, 1 row affected (0.17 sec)

3. 挿入したデータの確認

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

mysql> select *from DemoTable668;

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

+--------------------------+
| JoiningDate              |
+--------------------------+
| 2001-01-10T06:20:00.000Z |
| 2019-07-20T04:00:00.000Z |
| 2016-02-12T05:10:50.000Z |
+--------------------------+
3 rows in set (0.00 sec)

4. 日付形式の変換クエリを実行

いよいよ、日付形式をyyyy-mm-ddThh:mm:ss.sssZからyyyy-mm-dd hh:mm:ssへ変換するUPDATE文を実行します。

mysql> update DemoTable668
    SET JoiningDate = DATE_FORMAT(STR_TO_DATE(JoiningDate,'%Y-%m-%dT%H:%i:%s.000Z'),'%Y-%m-%d %H:%i:%s');
Query OK, 3 rows affected (0.11 sec)
Rows matched: 3 Changed: 3 Warnings: 0

クエリのポイント解説

• STR_TO_DATE()： 文字列として保存された日付を、指定したフォーマットに従って解析し、日時値に変換します。ここでは「%Y-%m-%dT%H:%i:%s.000Z」という入力形式を指定しています。
• DATE_FORMAT()： 解析された日時値を、目的の出力形式「%Y-%m-%d %H:%i:%s」（yyyy-mm-dd hh:mm:ss）に整形します。

5. 変換結果の確認

もう一度テーブルの内容を確認しましょう。

mysql> select *from DemoTable668;

実行結果は以下の通りです。「T」と「Z」が取り除かれ、標準的な日時形式に変換されていることがわかります。

+---------------------+
| JoiningDate         |
+---------------------+
| 2001-01-10 06:20:00 |
| 2019-07-20 04:00:00 |
| 2016-02-12 05:10:50 |
+---------------------+
3 rows in set (0.00 sec)

まとめ

JavaScriptなどから送信されるISO 8601形式の日付文字列（yyyy-mm-ddThh:mm:ss.sssZ）は、そのままではMySQLの日時関数で扱いにくい場合があります。STR_TO_DATE()で一度日時値に変換し、DATE_FORMAT()で目的の形式に整形することで、簡単に標準形式へ変換できます。なお、今後新規にテーブルを作成する場合は、VARCHARではなくDATETIME型を使用することで、こうした変換処理自体を不要にできる点も覚えておくと便利です。