MySQL查询 - 根据当前日期获取记录

Question

I am setting a Cron and I want to do various of functions on records which match the current day

My users table:

Username      registration_date    expiration_date
behz4d        2012-01-19 21:55:44  2013-01-23 11:15:24
test          2012-05-24 17:42:13  2013-04-12 17:23:19
...

Now I want to get the users which their expiration_date is TODAY I simply could query the tables, get their expiration_date in an array, and compare that date with today date, like:

$current_date = date("Y-m-d");
$query = mysql_query("SELECT username FROM users");
while($row = mysql_fetch_array($query)){
    $users_array[] = $row['username'] . "->" . substr($row['expiration_date'], 0, 10);// since I don't care about the exact time, I need to get the users who their expiration DAY is today 
}
foreach($users_array $username as $expiration_date){
    if($expiration_date == $current_date)
         $my_target_users[] = $username;
}

But this seems a little odd, I mean there should be another way...

Is there anyway that I could say like expiration_date = TODAY ?

Anybody could help me with this? Thanks in advance

---

UPDATE:

How about getting users who their expiration day is 3 days from today? I want to send an email to them and let them know that their account will be expired in 3 days... Should I just do:

... WHERE expiration_date >= CURRENT_DATE AND expiration_date < CURRENT_DATE + INTERVAL 3 DAYS

!?

Answer 1

SELECT username FROM users WHERE DATE(expiration_date) = CURRENT_DATE

However, this requires a(n inefficient) full table scan. If you have an index on expiration_date, you should instead use:

SELECT username
FROM   users
WHERE  expiration_date >= CURRENT_DATE
   AND expiration_date <  CURRENT_DATE + INTERVAL 1 DAY