MySQLでvarchar型をdatetime型に変換して比較する方法

MySQLでvarcharをdatetimeに変換して比較するには？

varchar型として保存された日時データをdatetime型に変換して比較するには、STR_TO_DATE()関数を使用します。この関数を使うことで、文字列形式の日付データを指定したフォーマットでdatetime型に変換できます。

ここでは、実際の例を通じて具体的な手順を解説します。

1. サンプルテーブルの作成

まず、日時データをvarchar型のカラムに格納するテーブルを作成します。

mysql> create table DemoTable1565
    -> (
    -> ArrivalDatetime varchar(40)
    -> );
Query OK, 0 rows affected (0.82 sec)

2. レコードの挿入

INSERTコマンドを使って、テーブルにいくつかのレコードを挿入します。ここでは「MM/DD/YYYY HH:MM:SS」形式の文字列を格納しています。

mysql> insert into DemoTable1565 values('10/01/2019 21:29:35');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable1565 values('10/13/2019 4:56:00');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1565 values('10/13/2018 12:40:46');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable1565 values('10/13/2019 21:30:00');
Query OK, 1 row affected (0.58 sec)

3. テーブル内の全レコードを表示

SELECT文で、テーブル内のすべてのレコードを確認します。

mysql> select * from DemoTable1565;

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

+---------------------+
| ArrivalDatetime     |
+---------------------+
| 10/01/2019 21:29:35 |
| 10/13/2019 4:56:00  |
| 10/13/2018 12:40:46 |
| 10/13/2019 21:30:00 |
+---------------------+
4 rows in set (0.00 sec)

4. 現在の日付を確認

比較対象となる現在の日付をNOW()関数で確認しておきましょう。

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-10-13 21:34:14 |
+---------------------+
1 row in set (0.00 sec)

5. varcharからdatetimeへの変換と比較クエリ

以下が、STR_TO_DATE()関数を使ってvarchar型をdatetime型に変換し、現在の日付と比較するクエリです。フォーマット指定子「%m/%d/%Y」により、「月/日/年」形式の文字列を正しく解析しています。

mysql> select * from DemoTable1565 where str_to_date(ArrivalDatetime,'%m/%d/%Y')=curdate();

実行結果は以下の通りです。現在の日付（2019-10-13）と一致するレコードのみが抽出されました。

+---------------------+
| ArrivalDatetime     |
+---------------------+
| 10/13/2019 4:56:00  |
| 10/13/2019 21:30:00 |
+---------------------+
2 rows in set, 4 warnings (0.03 sec)

補足：STR_TO_DATE()関数の主なフォーマット指定子

STR_TO_DATE()関数では、元の文字列の形式に合わせてフォーマット指定子を指定する必要があります。主な指定子は以下の通りです。

• %Y：4桁の年（例：2019）
• %m：2桁の月（01〜12）
• %d：2桁の日（01〜31）
• %H：24時間制の時（00〜23）
• %i：分（00〜59）
• %s：秒（00〜59）

なお、時刻部分まで含めて比較したい場合は、「'%m/%d/%Y %H:%i:%s'」のようにフォーマットを指定することで、時刻も正確に変換・比較できます。