MySQLの日時列から日付のみを抽出する方法

文字列として保存された日時データから日付部分だけを取り出したい場合は、DATE_FORMAT関数とSTR_TO_DATE関数を組み合わせることで実現できます。ここでは、「04:58 PM 10/31/2018」のような形式でvarchar型カラムに保存された日時から、日付のみを抽出する手順を具体的に解説します。

1. テーブルの作成

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

mysql> create table DemoTable
    (
    ShippingDate varchar(200)
     );
Query OK, 0 rows affected (0.25 sec)

2. レコードの挿入

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

mysql> insert into DemoTable values('04:58 PM 10/31/2018');
Query OK, 1 row affected (0.10 sec)

mysql> insert into DemoTable values('02:30 AM 01/01/2019');
Query OK, 1 row affected (0.07 sec)

mysql> insert into DemoTable values('12:01 AM 05/03/2019');
Query OK, 1 row affected (0.06 sec)

3. 登録データの確認

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

mysql> select *from DemoTable;

実行すると、次のような結果が出力されます。

+---------------------+
| ShippingDate        |
+---------------------+
| 04:58 PM 10/31/2018 |
| 02:30 AM 01/01/2019 |
| 12:01 AM 05/03/2019 |
+---------------------+
3 rows in set (0.00 sec)

4. 日付のみを抽出するクエリ

日時列から日付だけを選択するには、以下のクエリを実行します。

mysql> SELECT DATE_FORMAT(STR_TO_DATE(ShippingDate, '%h:%i %p %m/%d/%Y'), '%m/%d/%Y') from DemoTable;

実行結果を見ると、時刻部分が取り除かれ、日付のみが抽出されていることがわかります。

+-------------------------------------------------------------------------+
| DATE_FORMAT(STR_TO_DATE(ShippingDate, '%h:%i %p %m/%d/%Y'), '%m/%d/%Y') |
+-------------------------------------------------------------------------+
| 10/31/2018                                                              |
| 01/01/2019                                                              |
| 05/03/2019                                                              |
+-------------------------------------------------------------------------+
3 rows in set (0.00 sec)

クエリの仕組み

このクエリは、次の2段階の処理を行っています。

① STR_TO_DATE関数：文字列として保存された日時データを、フォーマット指定子「%h:%i %p %m/%d/%Y」（12時間制の時刻：分、AM/PM、月/日/年）に従って解析し、日付型へ変換します。

② DATE_FORMAT関数：変換された日付データを、今度は「%m/%d/%Y」（月/日/年）形式の文字列として整形して出力します。

補足：DATETIME型の場合

カラムがDATETIME型やDATE型として正しく保存されている場合は、STR_TO_DATEによる変換は不要です。SELECT DATE(ShippingDate) FROM DemoTable;のようにDATE関数を使うだけで、簡単に日付部分を取得できます。日時データは専用の型で保存しておくのが望ましい設計と言えるでしょう。