MySQLクエリで現在日付との差分を計算して並べ替え・表示する方法

はじめに

日付を「現在の日付からの差分」をもとに並べ替えて表示したい場合は、ORDER BY句を使用します。たとえば、まだ来ていない期限を近い順に上部へ表示し、すでに過ぎた期限は後ろにまとめたい、というようなケースで活躍するテクニックです。

現在の日付を確認する

まず、NOW()関数で現在の日時を確認しておきます。

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2019-06-09 21:08:16 |
+---------------------+
1 row in set (0.00 sec)

サンプルテーブルを作成する

次に、動作確認用のテーブルを作成しましょう。

mysql> create table DemoTable
    -> (
    -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> DueDate datetime
    -> );
Query OK, 0 rows affected (0.62 sec)

レコードを挿入する

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

mysql> insert into DemoTable(DueDate) values('2019-06-12');
Query OK, 1 row affected (0.24 sec)

mysql> insert into DemoTable(DueDate) values('2019-06-01');
Query OK, 1 row affected (0.37 sec)

mysql> insert into DemoTable(DueDate) values('2019-06-05');
Query OK, 1 row affected (0.20 sec)

mysql> insert into DemoTable(DueDate) values('2019-06-10');
Query OK, 1 row affected (0.13 sec)

mysql> insert into DemoTable(DueDate) values('2019-06-11');
Query OK, 1 row affected (0.66 sec)

登録したレコードを確認する

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

mysql> select *from DemoTable;

出力結果

+----+---------------------+
| Id | DueDate             |
+----+---------------------+
| 1  | 2019-06-12 00:00:00 |
| 2  | 2019-06-01 00:00:00 |
| 3  | 2019-06-05 00:00:00 |
| 4  | 2019-06-10 00:00:00 |
| 5  | 2019-06-11 00:00:00 |
+----+---------------------+
5 rows in set (0.00 sec)

差分を計算して並べ替えるクエリ

これが本題のクエリです。DATEDIFF()関数で現在の日付(CURDATE())との差分を求め、その値を基準に並べ替えます。差分は専用のカラムとして一緒に表示されます。

mysql> select Id,DueDate,DATEDIFF(DueDate, CURDATE()) AS t from DemoTable
    -> order by CASE WHEN t < 0 THEN 1 ELSE 0 END, t;

出力結果

+----+---------------------+------+
| Id | DueDate             | t    |
+----+---------------------+------+
| 4  | 2019-06-10 00:00:00 | 1    |
| 5  | 2019-06-11 00:00:00 | 2    |
| 1  | 2019-06-12 00:00:00 | 3    |
| 2  | 2019-06-01 00:00:00 | -8   |
| 3  | 2019-06-05 00:00:00 | -4   |
+----+---------------------+------+
5 rows in set (0.00 sec)

クエリのポイント解説

• DATEDIFF(DueDate, CURDATE()):期日と現在日付の差を日数で計算します。未来の日付なら正の値、過去の日付なら負の値になります。
• CASE WHEN t < 0 THEN 1 ELSE 0 END:差分が負(期限切れ)のレコードには1、正(まだ先)のレコードには0を返し、まずこのキーで並べ替えます。これにより、未来の期限を持つレコードが上位に集まります。
• 第2ソートキー「t」:同じグループ内では、日数差の昇順(現在日に近い順)で並びます。

この結果、出力例では現在日付(2019-06-09)に最も近い未来の期限「2019-06-10」が最初に表示され、以降は日付が遠い順に続き、期限切れのレコードが最後にまとめられます。タスク管理や予約システムなど、「対応すべきものから順に表示したい」という場面で応用できる便利な手法です。