MySQLで日付形式から数値の日付値を抽出する方法

MySQLで日付形式から数値の日付値を抽出する方法

MySQLで「31-Jan-19」のような文字列として保存された日付データを数値（UNIXタイムスタンプ）に変換したい場合は、UNIX_TIMESTAMP() 関数と STR_TO_DATE() 関数を組み合わせて使用します。

基本的な構文は以下の通りです。

select UNIX_TIMESTAMP(STR_TO_DATE(yourColumnName, "%d-%b-%y")) as anyAliasName from yourTableName;

サンプルテーブルの作成

まず、動作確認用のテーブルを作成します。

mysql> create table demo34
-> (
-> datevalue varchar(40)
-> );
Query OK, 0 rows affected (1.51 sec)

レコードの挿入

insertコマンドを使用して、いくつかのレコードをテーブルに挿入します。

mysql> insert into demo34 values('31-Jan-19');
Query OK, 1 row affected (0.13 sec)

mysql> insert into demo34 values('03-Mar-21');
Query OK, 1 row affected (0.12 sec)

mysql> insert into demo34 values('13-Jun-20');
Query OK, 1 row affected (0.11 sec)

テーブルの内容を確認

select文でテーブルからレコードを表示してみましょう。

mysql> select *from demo34;

このクエリは次の出力を返します。

+-----------+
| datevalue |
+-----------+
| 31-Jan-19 |
| 03-Mar-21 |
| 13-Jun-20 |
+-----------+
3 rows in set (0.00 sec)

数値の日付値を抽出するクエリ

日付形式から数値の日付値を抽出するには、以下のクエリを実行します。

mysql> select UNIX_TIMESTAMP(STR_TO_DATE(datevalue, "%d-%b-%y")) as Numeric_Date from demo34;

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

+--------------+
| Numeric_Date |
+--------------+
| 1548873000   |
| 1614709800   |
| 1591986600   |
+--------------+
3 rows in set (0.00 sec)

仕組みの解説

この処理では、まず STR_TO_DATE() のフォーマット指定子 "%d-%b-%y" によって、「日-月名-西暦下2桁」の形式を持つ文字列が DATE 型へ変換されます。続いて UNIX_TIMESTAMP() がその日付を、1970年1月1日00:00:00 UTCからの経過秒数であるUNIXタイムスタンプ（整数値）に変換します。これにより、文字列形式の日付をソートや計算に適した数値として扱えるようになります。