CODEWITHSUNDEEP
mysql
Sam Leurs
Sam Leurs

Reputation: 490

select all dates that are due in 7 days

I have the following query (which does not work):

SELECT user_id FROM due_dates WHERE due_date < DATE_ADD(CURDATE(),INTERVAL 1 WEEK)

I want to select all the user_id's where the due_date is due in exactly 1 week.

What my script does?

It sends an e-mail like "your account is due in 7 days".

Anyone knows how to fix this?

Upvotes: 0

Views: 97

Answers (3)

Arth
Arth

Reputation: 13110

I prefer the more natural shorthand.

If due_date is type DATE:

SELECT user_id
  FROM due_dates
 WHERE due_date = CURDATE() + INTERVAL 1 WEEK

If due_date is another temporal (DATETIME, TIMESTAMP):

SELECT user_id
  FROM due_dates
 WHERE due_date >= CURDATE() + INTERVAL 1 WEEK
   AND due_date < CURDATE() + INTERVAL 1 WEEK + INTERVAL 1 DAY

This allows the optimiser to use an available index on due_date, and prevents any 23:59:59 messiness when using the inclusive BETWEEN.

N.B. This will effectively convert a temporal to a date before doing a comparison. A row with a due_date of 2000-01-08 10:00:00 will be considered 7 days after a NOW() of 2000-01-01 xx:xx:xx and returned.

In @BerndBuffen's answer, if NOW() returns 2000-01-01 09:00:00, the row will be filtered out, and if NOW() returns 2000-01-02 09:00:00, the row will be returned.

It's a subtle but important difference.

Upvotes: 0

Bernd Buffen
Bernd Buffen

Reputation: 15057

use this query. if you use BETWEEN MySQL can use a index on due_date else it is a FULL TABLE SCAN.

SELECT user_id
FROM due_dates
WHERE due_date BETWEEN 
    timestamp(date (now() -interval 1 week))
AND
    timestamp(date(now() - interval 1 week + interval 1 day));

SAMPLE

mysql> select * from due_dates;
+---------+---------------------+
| user_id | due_date            |
+---------+---------------------+
|      13 | 2015-10-16 01:00:00 |
|      14 | 2015-10-16 05:00:00 |
|      15 | 2015-10-17 04:00:00 |
|      16 | 2015-10-17 05:00:00 |
|      17 | 2015-10-18 01:00:00 |
|      18 | 2015-10-19 01:00:00 |
|      19 | 2015-11-16 01:00:00 |
|      20 | 2015-11-16 05:00:00 |
|      21 | 2015-11-17 04:00:00 |
|      22 | 2015-11-17 05:00:00 |
|      23 | 2015-11-18 01:00:00 |
|      24 | 2015-11-19 01:00:00 |
|       7 | 2016-10-16 01:00:00 |
|       8 | 2016-10-16 05:00:00 |
|       9 | 2016-10-17 04:00:00 |
|      10 | 2016-10-17 05:00:00 |
|      11 | 2016-10-18 01:00:00 |
|      12 | 2016-10-19 01:00:00 |
|       1 | 2016-11-16 01:00:00 |
|       2 | 2016-11-16 05:00:00 |
|       3 | 2016-11-17 04:00:00 |
|       4 | 2016-11-17 05:00:00 |
|       5 | 2016-11-18 01:00:00 |
|       6 | 2016-11-19 01:00:00 |
+---------+---------------------+
24 rows in set (0,00 sec)

mysql> SELECT user_id
    -> FROM due_dates
    -> WHERE due_date BETWEEN
    -> timestamp(date (now() -interval 1 week))
    -> AND
    -> timestamp(date(now() - interval 1 week + interval 1 day));
+---------+
| user_id |
+---------+
|       3 |
|       4 |
+---------+
2 rows in set (0,00 sec)

mysql> EXPLAIN SELECT user_id FROM due_dates WHERE due_date BETWEEN  timestamp(date (now() -interval 1 week)) AND timestamp(date(now() - interval 1 week + interval 1 day));
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | due_dates | NULL       | range | due_date      | due_date | 5       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0,00 sec)

mysql>

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522787

Try this query:

SELECT user_id
FROM due_dates
WHERE DATE(due_date) = DATE_ADD(CURDATE(),INTERVAL 1 WEEK)
-- if due_date is already DATE type then the following should work:
-- WHERE due_date = DATE_ADD(CURDATE(),INTERVAL 1 WEEK)

Upvotes: 0

Related Questions