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

MySQLでは、UNIX_TIMESTAMP() 関数を使用することで、日時の値をUnixタイムスタンプに変換できます。Unixタイムスタンプとは、1970年1月1日00:00:00 UTCからの経過秒数を表す数値で、システム間での日時データのやり取りに広く使われています。

UNIX_TIMESTAMP()関数で日時をUnixタイムスタンプに変換する

まず、STR_TO_DATE()関数で文字列の日時を日付型に変換し、その結果をUNIX_TIMESTAMP()関数に渡すことで、Unixタイムスタンプを取得できます。

以下がそのクエリの例です。

mysql> SELECT UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p'));

このクエリを実行すると、結果は日付形式ではなく、数値形式のUnixタイムスタンプとして返されます。以下が出力例です。

+------------------------------------------------------------------------+
| UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p')) |
+------------------------------------------------------------------------+
|                                                             1539966600 |
+------------------------------------------------------------------------+
1 row in set (0.04 sec)

このように、1539966600 という数値がUnixタイムスタンプとして出力されました。

Unixタイムスタンプから元の日時を取得する

逆に、Unixタイムスタンプから実際の日時を取得したい場合は、FROM_UNIXTIME()関数を使用します。UNIX_TIMESTAMP()と組み合わせることで、文字列の日時を一度タイムスタンプに変換し、任意のフォーマットで表示することも可能です。

以下がそのクエリです。

mysql> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i:%p');

以下が出力結果です。

+-----------------------------------------------------------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(STR_TO_DATE('Oct 19 2018 10:00PM', '%M %d %Y %h:%i%p')),'%m-%d-%Y %h:%i:%p') |
+-----------------------------------------------------------------------------------------------------------+
| 10-19-2018 10:00:PM                                                                                       |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

主なフォーマット指定子の説明

上記のクエリで使用されている主なフォーマット指定子は以下の通りです。

• %M: 月の完全な英語名（例：October）
• %d: 日（2桁の数値）
• %Y: 4桁の年
• %h: 時間（12時間制）
• %i: 分
• %p: 午前/午後（AM/PM）
• %m: 月（数値形式）

これらの関数を活用することで、MySQL上で日時データとUnixタイムスタンプを自由に相互変換でき、API連携やログ管理など、さまざまな場面で役立ちます。