MySQLでUnixタイムスタンプを日付形式に変換する方法

MySQLでは、bigint型などで保存されたUnixタイムスタンプ（エポック秒）を、人が読みやすい日付形式に変換できます。そのためには、from_unixtime()関数とdate_format()関数を組み合わせて使用します。

基本構文

SELECT DATE_FORMAT(FROM_UNIXTIME(カラム名), '%b %d, %Y %l:%i %p PDT') FROM テーブル名;

この構文では、まずFROM_UNIXTIME()がUnixタイムスタンプをDATETIME型に変換し、続いてDATE_FORMAT()が指定したフォーマット文字列に従って日付を整形します。フォーマット指定子の意味は以下のとおりです。

• %b: 月の省略形（Jan、Febなど）
• %d: 日（2桁表示）
• %Y: 4桁の年
• %l: 時（12時間制）
• %i: 分
• %p: AMまたはPM

動作確認用テーブルの作成

実際に構文を理解するために、サンプルテーブルを作成しましょう。テーブル作成クエリは次のとおりです。

mysql> CREATE TABLE unixTimeStampFormatDemo
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> MyTimeStampValue bigint
    -> );
Query OK, 0 rows affected (0.59 sec)

レコードの挿入

INSERTコマンドを使って、いくつかのUnixタイムスタンプを登録します。

mysql> INSERT INTO unixTimeStampFormatDemo(MyTimeStampValue) VALUES(1334428200);
Query OK, 1 row affected (0.20 sec)
mysql> INSERT INTO unixTimeStampFormatDemo(MyTimeStampValue) VALUES(1513881000);
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO unixTimeStampFormatDemo(MyTimeStampValue) VALUES(1542738600);
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO unixTimeStampFormatDemo(MyTimeStampValue) VALUES(1551637800);
Query OK, 1 row affected (0.15 sec)

登録データの確認

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

mysql> SELECT * FROM unixTimeStampFormatDemo;

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

+----+------------------+
| Id | MyTimeStampValue |
+----+------------------+
|  1 | 1334428200       |
|  2 | 1513881000       |
|  3 | 1542738600       |
|  4 | 1551637800       |
+----+------------------+
4 rows in set (0.00 sec)

Unixタイムスタンプを日付形式に変換するクエリ

それでは、保存されたUnixタイムスタンプを読みやすい日時形式に変換してみます。

mysql> SELECT DATE_FORMAT(FROM_UNIXTIME(MyTimeStampValue), '%b %d, %Y %l:%i %p PDT') FROM unixTimeStampFormatDemo;

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

+-----------------------------------------------------------------------+
| DATE_FORMAT(FROM_UNIXTIME(MyTimeStampValue), '%b %d, %Y %l:%i %p PDT') |
+-----------------------------------------------------------------------+
| Apr 15, 2012 12:00 AM PDT                                             |
| Dec 22, 2017 12:00 AM PDT                                             |
| Nov 21, 2018 12:00 AM PDT                                             |
| Mar 04, 2019 12:00 AM PDT                                             |
+-----------------------------------------------------------------------+
4 rows in set (0.00 sec)

このように、FROM_UNIXTIME()とDATE_FORMAT()を組み合わせることで、数値だけのUnixタイムスタンプを「Apr 15, 2012 12:00 AM PDT」のような見やすい形式に変換できます。なお、サンプル出力ではタイムゾーンとしてPDT（太平洋夏時間）を表記していますが、実際の変換結果はサーバーのタイムゾーン設定に依存する点に注意してください。日本時間で表示したい場合は、あらかじめSET time_zone = '+09:00';を実行しておくとよいでしょう。