月日のみの範囲を指定したクエリ

Question

###前提・実現したいこと
DATE型で日付が保存されているカラムを対象に、今日から数日の日付を含む要素のみ抽出するクエリを作成したい。

###発生している問題・エラーメッセージ
※日付を保存しているカラム名は"MMDD"でDATE型

select * from A where DATE_FORMAT(MMDD, '%m%d') between DATE_FORMAT('2014-01-01', '%m%d') and DATE_FORMAT('2015-02-01', '%m%d')

上記コードの場合、特に問題なく抽出が可能なのですが

select * from A where DATE_FORMAT(MMDD, '%m%d') between DATE_FORMAT('2014-12-01', '%m%d') and DATE_FORMAT('2015-02-01', '%m%d')

このように、12月～1月をまたぐような期間を指定した場合、返り値が0件となってしまいます。
この場合は12/01～02/01の日付を持つ要素だけ抽出したいです。

###補足情報(言語/FW/ツール等のバージョンなど)
MySQLにて作成しています。

Answer 1

月日(MMDD)の情報のみで判定しているため、1201 <= 対象の日付 < 0201 となり絶対に条件を満たさないようになってしまっています。年(yyyy)の情報も判定に使用すると解決すると思います。

Comments

[wattaru]

MMDDカラムはDate型なので年(yyyy)の情報も抽出できると思います。

Answer 2

ユーザーの登録日や誕生日のみの情報が欲しく、登録年は考慮しない

今日基準から何日間、という判定

要するに、
「現在日から N日以内に誕生日を迎えるユーザーを抽出したい」
と言うことでしょうか？

であれば、例えば 30日以内に誕生日を迎えるユーザーを抽出する場合、以下のような SQL文で実現できます。

SELECT *
FROM A
WHERE MAKEDATE(YEAR(CURDATE())    , DAYOFYEAR(MMDD)) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
   OR MAKEDATE(YEAR(CURDATE()) + 1, DAYOFYEAR(MMDD)) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY);

参考:
http://stackoverflow.com/questions/8812280/birthdays-dates-between-two-months

実行結果

mysql> CREATE TABLE A (MMDD DATE);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO A VALUES ('2000-01-01'), ('1990-05-12'), ('2010-06-11'), ('1980-06-12'), ('2017-02-01');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM A;
+------------+
| MMDD       |
+------------+
| 2000-01-01 |
| 1990-05-12 |
| 2010-06-11 |
| 1980-06-12 |
| 2017-02-01 |
+------------+
5 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM A
    -> WHERE MAKEDATE(YEAR(CURDATE())    , DAYOFYEAR(MMDD)) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
    ->    OR MAKEDATE(YEAR(CURDATE()) + 1, DAYOFYEAR(MMDD)) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY);
+------------+
| MMDD       |
+------------+
| 1990-05-12 |
| 2010-06-11 |
+------------+
2 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM A
    -> WHERE MAKEDATE(YEAR(CURDATE())    , DAYOFYEAR(MMDD)) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 250 DAY)
    ->    OR MAKEDATE(YEAR(CURDATE()) + 1, DAYOFYEAR(MMDD)) BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 250 DAY);
+------------+
| MMDD       |
+------------+
| 2000-01-01 |
| 1990-05-12 |
| 2010-06-11 |
| 1980-06-12 |
+------------+
4 rows in set (0.00 sec)

Answer 3

月日だけぬきださなくては行けない理由はなにかあるのでしょうか？
たとえば5/12から前後３日分(5/9-15)のデータを抽出するとして
去年も一昨年も場合によっては未来日も抽出する必要があるのでしょうか？

'2014-01-01'から'2015-02-01'が１年以上の期間をしていしても1/1-2/1なのに
'2014-12-01'から'2015-02-01'が12/1-2/1というのはちょっと道理が合わない気がします
2/1-12/1じゃないのでしょうか？

一応やり方としては@xが@a-@b間だとして
@a<=@bなら@x between @a and @b
@a>@bなら(@a<=@x or @b<=@x)とすれば拾えますが

Comments

[Praline]

やはり年を超えたか判定するのが無難でしょうか…

ユーザーの登録日や誕生日のみの情報が欲しく、登録年は考慮しない処理を考えていました。

[yambejp]

では例えば
'2014-01-01'から'2014-02-01'が1/1~2/１だとして前後をひっくり返して
'2014-02-01'から'2014-01-01'とした場合は、2/1~12/31+1/1でよいのですね？

[Praline]

その認識で問題ないです。
ただ、今日基準から何日間、という判定にするので、基本的にはそのような運用は発生しない前提です。

Answer 4

where区でifを使いましたが...

select * from A
where if ( DATE_FORMAT('2014-01-01', '%m%d') <= DATE_FORMAT('2015-02-01', '%m%d'),
 (DATE_FORMAT(MMDD, '%m%d') between DATE_FORMAT('2014-01-01', '%m%d') and DATE_FORMAT('2015-02-01', '%m%d')),
 (DATE_FORMAT(MMDD, '%m%d') >= DATE_FORMAT('2014-01-01', '%m%d') OR
  DATE_FORMAT(MMDD, '%m%d') <= DATE_FORMAT('2015-02-01', '%m%d'))
);

select * from A
where if ( DATE_FORMAT('2014-12-01', '%m%d') <= DATE_FORMAT('2015-02-01', '%m%d'),
 (DATE_FORMAT(MMDD, '%m%d') between DATE_FORMAT('2014-12-01', '%m%d') and DATE_FORMAT('2015-02-01', '%m%d')),
 (DATE_FORMAT(MMDD, '%m%d') >= DATE_FORMAT('2014-12-01', '%m%d') OR
  DATE_FORMAT(MMDD, '%m%d') <= DATE_FORMAT('2015-02-01', '%m%d'))
);