MySQLでタイムスタンプを降順に並べ替え、「0000-00-00 00:00:00」だけを先頭に表示する方法

MySQLでORDER BY句を使いタイムスタンプを降順ソートすると、通常は無効な日付である「0000-00-00 00:00:00」は最後尾に配置されます。しかし、ケースによってはこのゼロのタイムスタンプだけを先頭に表示したいこともあるでしょう。本記事では、そのような並べ替えを1つのクエリで実現する方法を解説します。

サンプルテーブルの作成

まず、以下のコマンドでテーブルを作成します。

mysql> create table DemoTable
(
    `timestamp` timestamp
);
Query OK, 0 rows affected (1.12 sec)

レコードの挿入

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

mysql> insert into DemoTable values(now());
Query OK, 1 row affected (0.27 sec)
mysql> insert into DemoTable values('00:00:00');
Query OK, 1 row affected (0.73 sec)
mysql> insert into DemoTable values('2018-01-10 12:34:45');
Query OK, 1 row affected (0.80 sec)
mysql> insert into DemoTable values('2019-12-31 10:50:45');
Query OK, 1 row affected (0.84 sec)

テーブルの内容を確認する

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

mysql> select *from DemoTable;

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

+---------------------+
| timestamp           |
+---------------------+
| 2019-08-17 06:17:12 |
| 0000-00-00 00:00:00 |
| 2018-01-10 12:34:45 |
| 2019-12-31 10:50:45 |
+---------------------+
4 rows in set (0.00 sec)

ゼロのタイムスタンプを先頭にして降順ソートするクエリ

以下が、ゼロのタイムスタンプを最初に表示しながら、残りのタイムスタンプを降順で並べ替えるクエリです。

mysql> select *from DemoTable order by (`timestamp`=0) DESC,`timestamp` DESC;

実行結果：

+---------------------+
| timestamp           |
+---------------------+
| 0000-00-00 00:00:00 |
| 2019-12-31 10:50:45 |
| 2019-08-17 06:17:12 |
| 2018-01-10 12:34:45 |
+---------------------+
4 rows in set (0.00 sec)

仕組みの解説

このクエリのポイントは、第1ソートキーに指定した(`timestamp`=0)という条件式です。MySQLでは比較条件はブール値として評価され、タイムスタンプがゼロなら1（真）、それ以外なら0（偽）を返します。この値をDESCで並べることで、ゼロのタイムスタンプが必ず最上部に来ます。続く第2ソートキーで`timestamp`自体をDESCにしているため、残りの有効な日時は新しい順に整列されるという仕組みです。

なお、MySQL 5.7以降のデフォルト設定（STRICTモードやNO_ZERO_DATEオプション）では、「0000-00-00 00:00:00」の挿入自体が制限される場合があります。そのような環境では、代わりにNULLを格納し、IS NULLで判定して同様の並べ替えを行う方法も検討するとよいでしょう。