MySQL Query - 根据当前日期获取记录 [英] MySQL Query - get records based on current date
问题描述
我正在设置一个 Cron,我想对与当天匹配的记录执行各种功能
I am setting a Cron and I want to do various of functions on records which match the current day
我的users
表:
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
...
现在我想获取他们的 expiration_date
是 TODAY
的用户我可以简单地查询表,在数组中获取它们的 expiration_date
,然后将该日期与今天的日期进行比较,例如:
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...
无论如何我可以说expiration_date = TODAY
?
有人可以帮我解决这个问题吗?提前致谢
Anybody could help me with this? Thanks in advance
更新:
如何让用户的到期日是从今天起 3 天?我想给他们发一封电子邮件,让他们知道他们的帐户将在 3 天后过期...我应该这样做吗:
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
!?
推荐答案
SELECT username FROM users WHERE DATE(expiration_date) = CURRENT_DATE
然而,这需要(n个低效)全表扫描.如果您在 expiration_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
这篇关于MySQL Query - 根据当前日期获取记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!