はじめに
MySQLでdatetime型のレコードからある期間内の特定の時間範囲と曜日を指定してレコードを取り出すのにつまづいたので書き留めます。
やりたいこと
更新日時が2019年2月～3月かつ
土日以外かつ9:00～18:00のレコードを取り出したい。
MySQLのバージョンは5.6.33で行いました。
日付と時間の指定
CAST関数を使用しました。
CASTは値の型の変換を行う関数です。
書き方
CAST カラム名 AS 型
曜日の指定
WEEKDAY関数を使用しました。
WEEKDAYは曜日を調べる関数です。
引数に日時を渡すと返り値で曜日の数値が返ってきます。
書き方
WEEKDAY(日時)
| 返り値 | 曜日 |
|---|---|
| 0 | 月曜日 |
| 1 | 火曜日 |
| 2 | 水曜日 |
| 3 | 木曜日 |
| 4 | 金曜日 |
| 5 | 土曜日 |
| 6 | 日曜日 |
実行
まずはテーブルの中身を確認
Select * from users;
テーブルの内容は以下のようになっています。
+----+--------------------+---------------------+
| id | name | updated_at |
+----+--------------------+---------------------+
| 1 | イチロウ | 2019-03-19 17:00:49 |
| 2 | ジロウ | 2019-04-19 15:48:58 |
| 3 | サブロウ | 2019-02-27 21:44:50 |
| 4 | シロウ | 2019-03-02 14:51:03 |
| 5 | ゴロウ | 2019-02-18 14:38:01 |
| 6 | ロクロウ | 2019-03-12 13:06:25 |
| 7 | ナナロウ | 2019-03-16 05:39:16 |
| 8 | ハチロウ | 2019-02-26 14:33:30 |
| 9 | キュウロウ | 2019-02-18 12:24:37 |
| 10 | ジュウロウ | 2019-02-28 17:52:23 |
+----+--------------------+---------------------+
次に2019年2~3月の9:00~18:00かつ土日以外のレコードを抽出します。
Select * from users where
cast(updated_at as date) BETWEEN '2019-02-01' AND '2019-03-31'
AND cast(updated_at as TIME) BETWEEN '09:00:00' AND '18:00:00'
AND WEEKDAY(updated_at) NOT IN (5, 6);
cast(updated_at as date) BETWEEN '2019-02-01' AND '2019-03-31'
⇒datetime型をdate型に変換し、日付範囲を指定
cast(updated_at as TIME) BETWEEN '09:00:00' AND '18:00:00'
⇒datetime型をtime型に変換し、時間範囲を指定
WEEKDAY(updated_at) NOT IN (5, 6)
⇒WEEKDAY関数にupdated_atを渡し、返り値が5,6（土日）以外であるかを判定
実行結果は以下の通りです。
+----+--------------------+---------------------+
| id | name | updated_at |
+----+--------------------+---------------------+
| 1 | イチロウ | 2019-03-19 17:00:49 |
| 5 | ゴロウ | 2019-02-18 14:38:01 |
| 6 | ロクロウ | 2019-03-12 13:06:25 |
| 8 | ハチロウ | 2019-02-26 14:33:30 |
| 9 | キュウロウ | 2019-02-18 12:24:37 |
| 10 | ジュウロウ | 2019-02-28 17:52:23 |
+----+--------------------+---------------------+
以下のNGが書いてあるレコード以外が取れていることが確認できます。
+----+--------------------+---------------------+
| id | name | updated_at |
+----+--------------------+---------------------+
| 1 | イチロウ | 2019-03-19 17:00:49 |
| 2 | ジロウ | 2019-04-19 15:48:58 | 期間NG
| 3 | サブロウ | 2019-02-27 21:44:50 | 時間NG
| 4 | シロウ | 2019-03-02 14:51:03 | 曜日NG
| 5 | ゴロウ | 2019-02-18 14:38:01 |
| 6 | ロクロウ | 2019-03-12 13:06:25 |
| 7 | ナナロウ | 2019-03-16 05:39:16 | 曜日NG、時間NG
| 8 | ハチロウ | 2019-02-26 14:33:30 |
| 9 | キュウロウ | 2019-02-18 12:24:37 |
| 10 | ジュウロウ | 2019-02-28 17:52:23 |
+----+--------------------+---------------------+
以上。