MySQLでdd/mm/yyyy形式の文字列をUnixタイムスタンプに変換する方法

dd/mm/yyyy形式の文字列をUnixタイムスタンプへ変換する方法

MySQLでは、UNIX_TIMESTAMP()関数とSTR_TO_DATE()関数を組み合わせることで、「dd/mm/yyyy」（日/月/年）形式の日付文字列をUnixタイムスタンプに変換できます。基本構文は以下の通りです。

SELECT UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName,'%d/%m/%Y')) as anyVariableName FROM yourTableName;

STR_TO_DATE()がフォーマット指定子「%d/%m/%Y」に従って文字列をDATE型に変換し、続くUNIX_TIMESTAMP()がその日付をUnixエポック（1970年1月1日 00:00:00 UTC）からの経過秒数に変換します。

サンプルテーブルの作成

構文の動作を確認するため、まずテーブルを作成します。テーブル作成クエリは以下の通りです。

mysql> create table ConvertddmmyyyyInUnixTimeStamp
    -> (
    -> Id int NOT NULL AUTO_INCREMENT,
    -> Created_at varchar(30),
    -> PRIMARY KEY(Id)
    -> );
Query OK, 0 rows affected (0.57 sec)

レコードの挿入

次に、INSERTコマンドを使って複数のレコードをテーブルに挿入します。

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('10/11/2012');
Query OK, 1 row affected (0.21 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('11/12/2013');
Query OK, 1 row affected (0.23 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('10/12/2012');
Query OK, 1 row affected (0.12 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('31/01/2015');
Query OK, 1 row affected (0.19 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('24/04/2016');
Query OK, 1 row affected (0.21 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('20/09/2017');
Query OK, 1 row affected (0.26 sec)

mysql> insert into ConvertddmmyyyyInUnixTimeStamp(Created_at) values('15/03/2018');
Query OK, 1 row affected (0.14 sec)

登録データの確認

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

mysql> select *from ConvertddmmyyyyInUnixTimeStamp;

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

+----+------------+
| Id | Created_at |
+----+------------+
| 1  | 10/11/2012 |
| 2  | 11/12/2013 |
| 3  | 10/12/2012 |
| 4  | 31/01/2015 |
| 5  | 24/04/2016 |
| 6  | 20/09/2017 |
| 7  | 15/03/2018 |
+----+------------+
7 rows in set (0.00 sec)

dd/mm/yyyy文字列をUnixタイムスタンプに変換する

それでは、実際にdd/mm/yyyy形式の文字列をUnixタイムスタンプへ変換してみましょう。

mysql> select unix_timestamp(str_to_date(Created_at,'%d/%m/%Y')) as UnixTimestamp from ConvertddmmyyyyInUnixTimeStamp;

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

+---------------+
| UnixTimestamp |
+---------------+
| 1352485800    |
| 1386700200    |
| 1355077800    |
| 1422642600    |
| 1461436200    |
| 1505845800    |
| 1521052200    |
+---------------+
7 rows in set (0.00 sec)

まとめと注意点

このように、STR_TO_DATE()でフォーマット指定子を明示的に指定して文字列を日付に変換し、その結果をUNIX_TIMESTAMP()に渡すだけで、任意の形式の日付文字列からUnixタイムスタンプを取得できます。

なお、UNIX_TIMESTAMP()の戻り値はセッションのタイムゾーン設定の影響を受けるため、環境によって値が異なる場合があります。また、本番環境ではVARCHAR型ではなくDATE型やDATETIME型で日付を保存しておくことで、変換処理の手間を省き、検索性能も向上させることができます。