MySQLでDATETIME型を数値に変換する方法｜UNIX_TIMESTAMP関数の使い方を解説

MySQLで日時（DATETIME型）のデータを数値に変換したい場合は、UNIX_TIMESTAMP()関数を使用します。この関数は、指定した日時を「1970年1月1日 00:00:00（UTC）」からの経過秒数であるUnixタイムスタンプとして返します。

基本構文

DATETIMEを数値に変換する際の基本構文は以下の通りです。

SELECT UNIX_TIMESTAMP(yourColumnName) AS anyVariableName FROM yourTableName;

それでは、実際にどのように動作するのか、サンプルテーブルを作成して確認していきましょう。

サンプルテーブルの作成

まず、テスト用のテーブルを作成します。テーブル作成のクエリは以下の通りです。

mysql> CREATE TABLE DateTimeToNumberDemo
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> releasedDate datetime,
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.46 sec)

テストデータの挿入

次に、INSERT文を使って複数のレコードを挿入します。現在の日時を返す now() や当日の日付を返す curdate() のほか、任意の日付文字列もそのまま登録できます。

mysql> INSERT INTO DateTimeToNumberDemo(releasedDate) VALUES(now());
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO DateTimeToNumberDemo(releasedDate) VALUES(curdate());
Query OK, 1 row affected (0.24 sec)

mysql> INSERT INTO DateTimeToNumberDemo(releasedDate) VALUES('1978-01-19');
Query OK, 1 row affected (0.55 sec)

mysql> INSERT INTO DateTimeToNumberDemo(releasedDate) VALUES('2016-09-13');
Query OK, 1 row affected (0.46 sec)

mysql> INSERT INTO DateTimeToNumberDemo(releasedDate) VALUES('2017-11-12');
Query OK, 1 row affected (0.22 sec)

mysql> INSERT INTO DateTimeToNumberDemo(releasedDate) VALUES('2018-12-09');
Query OK, 1 row affected (0.21 sec)

登録済みデータの確認

SELECT文ですべてのレコードを表示し、正しく登録されているかを確認します。

mysql> SELECT *FROM DateTimeToNumberDemo;

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

+----+---------------------+
| Id | releasedDate        |
+----+---------------------+
|  1 | 2019-01-12 21:20:57 |
|  2 | 2019-01-12 00:00:00 |
|  3 | 1978-01-19 00:00:00 |
|  4 | 2016-09-13 00:00:00 |
|  5 | 2017-11-12 00:00:00 |
|  6 | 2018-12-09 00:00:00 |
+----+---------------------+
6 rows in set (0.00 sec)

DATETIMEを数値に変換するクエリ

いよいよ、releasedDate 列の値を数値（Unixタイムスタンプ）へ変換します。使用するクエリは以下の通りです。

mysql> SELECT unix_timestamp(releasedDate) AS DateToNumber FROM DateTimeToNumberDemo;

実行結果は以下のようになります。

+--------------+
| DateToNumber |
+--------------+
|   1547308257 |
|   1547231400 |
|    253996200 |
|   1473705000 |
|   1510425000 |
|   1544293800 |
+--------------+
6 rows in set (0.00 sec)

このように、すべての日時データが秒単位の数値に変換されました。日時を数値として比較・ソート・計算したい場合などに非常に便利です。

補足：タイムゾーンと逆変換について

UNIX_TIMESTAMP() の戻り値は、MySQLセッションの time_zone 設定の影響を受ける点に注意してください。サーバーとアプリケーションでタイムゾーンが異なると、意図した値にならないことがあります。

また、逆に数値（Unixタイムスタンプ）から日時に戻したい場合は、FROM_UNIXTIME() 関数を使用します。

mysql> SELECT FROM_UNIXTIME(1547308257);
+---------------------------+
| FROM_UNIXTIME(1547308257) |
+---------------------------+
| 2019-01-12 21:20:57       |
+---------------------------+