MySQLにおけるUNIX_TIMESTAMPの最適なデータ型とは？

UNIX_TIMESTAMPに最適なデータ型は整数型（INT）

MySQLでUNIX_TIMESTAMPの値を保存する場合、最も適しているのは整数型（INTEGER）です。具体的には、次のように定義します。

int(11);

整数型は「>」や「<=」といった比較条件を使った検索や、インデックスの作成に適しています。さらに、UNIX_TIMESTAMP関数の戻り値自体が整数であるため、データ型としても相性が良いのがポイントです。

DATETIMEをUNIXタイムスタンプに変換してみよう

ここからは、DATETIME型の値をUNIXタイムスタンプに変換したときに、実際どのような結果が得られるのかを順番に確認していきましょう。

1. テーブルを作成する

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

mysql> create table UnixTime
    -> (
    -> DueTime datetime
    -> );
Query OK, 0 rows affected (0.55 sec)

2. レコードを挿入する

続いて、INSERTコマンドを使って日付データを登録します。

mysql> insert into UnixTime values(now());
Query OK, 1 row affected (0.15 sec)

mysql> insert into UnixTime values('2010-10-14');
Query OK, 1 row affected (0.15 sec)

mysql> insert into UnixTime values('2020-09-24');
Query OK, 1 row affected (0.15 sec)

3. 登録されたデータを確認する

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

mysql> select *from UnixTime;

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

+---------------------+
| DueTime             |
+---------------------+
| 2018-12-19 10:07:11 |
| 2010-10-14 00:00:00 |
| 2020-09-24 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

4. DATETIMEをUNIXタイムスタンプに変換する

それでは、DATETIME型の値をUNIXタイムスタンプへ変換するクエリを見てみましょう。

mysql> select unix_timestamp(DueTime) as Output from UnixTime;

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

+------------+
| Output     |
+------------+
| 1545194231 |
| 1286994600 |
| 1600885800 |
+------------+
3 rows in set (0.00 sec)

このように、DATETIME型の値が10桁の整数値（UNIXタイムスタンプ）に変換されていることがわかります。

補足：2038年問題への注意点

符号付きINT型で表現できる最大値は「2147483647」で、これは2038年1月19日の日時に相当します。それ以降の日時を扱う予定がある場合は、BIGINT型やUNSIGNED属性の利用を検討すると安心です。用途に応じて適切なデータ型を選択しましょう。