PHP从MySQL中选择，日期字段为将来的7天 [英] PHP Select from MySQL where date field is 7 days in the future

问题描述

我有一个自动检查器，用于检查在接下来的7天内将要过期的域，并发送并通过电子邮件发送给客户.

I have an automatic checker that checks for domains that are going to expire within the next 7 days and it sends and email to the customer.

我正在使用此SQL查询:

Im using this SQL Query:

SELECT * from domain_names where status = '' or status = 'valid' and expiry_date = DATE(NOW() + INTERVAL 7 DAY)

，但无法正常工作.我需要它来检查expirey_date确切地说是未来7天的行.此外，如果检查程序停止运行，并且在未运行的时间内错过了一些行，它也需要执行这些行

but its not working correctly. i need it to check for rows with an expiry_date that is 7 days in the future exactly. also if the checker stops running and it misses some rows for the amount of time its not running it needs to do those rows too

推荐答案

您可能已将expiry_date定义为日期时间值，这意味着您的比较不正确.例如您需要使用

You've probably defined expiry_date as a datetime value, which means your comparisons are incorrect. e.g. you need to use

SELECT ... WHERE date(expiry_date) = date(now() + interval 7 day)

相反(请注意在date()操作中将+7天换行.

instead (note the wrapping of the +7 day in a date() operation.

例如

给出一个带有日期和日期时间字段的表:

Given a table with a date and a datetime field:

+------------+---------------------+
| d          | dt                  |
+------------+---------------------+
| 2013-06-28 | 2013-06-28 08:23:03 |
+------------+---------------------+

注意比较结果:

mysql> select d=now(), d=date(now()), dt=now(), dt=date(now()), now() from x;
+---------+---------------+----------+----------------+---------------------+
| d=now() | d=date(now()) | dt=now() | dt=date(now()) | now()               |
+---------+---------------+----------+----------------+---------------------+
|       0 |             1 |        0 |              0 | 2013-06-28 08:26:20 |
+---------+---------------+----------+----------------+---------------------+
1 row in set (0.00 sec)

日期与日期日期时间=假
日期与日期=真
日期时间datetime = false(hh:mm:ss不匹配，因此不相等)
日期时间date = false(日期扩展为yyyy-mm-hh 00:00:00，而hh:mm:ss不匹配

date v.s. datetime = false
date v.s date = true
datetime v.s. datetime = false (hh:mm:ss doesn't match, so not equal)
datetime v.s. date = false (date is expanded out to yyyy-mm-hh 00:00:00 and the hh:mm:ss don't match

这篇关于PHP从MySQL中选择，日期字段为将来的7天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！