适用于 mysql 的 sql 查询中的日期逻辑

Question

嗨，我对我的 MySQL 查询中的日期逻辑完全感到困惑，因为每天上午 12：00 运行 cron 作业

我正在一个汽车上市网站上工作，其中的汽车列表以 mysql 日期时间格式具有到期日期。

所有过期的列表将在到期日期后7天后从网站上删除

当 cron 作业运行时，它会执行以下操作

任务 1 - 向用户发送电子邮件警报，告知他们其列表已过期。

因此，我需要选择自上次运行 cron 作业以来已过期的所有列表，并且不包括在此之前的列表，以便每个列表仅发送一次到期警报电子邮件。

我尝试对此任务进行以下sql查询（再次对此感到困惑）

SELECT car_id FROM cars WHERE expiry_date > DATE_SUB(NOW(), INTERVAL 1 DAY) AND expiry_date < NOW()

任务 2 - 将向用户发送电子邮件警报，告知他们将在 24 小时后永久删除列表。

因此，我需要选择所有将在过期后超过 24 小时/6 天内删除的列表，我需要确保它们至少有 24 小时的时间来续订它们。此外，我需要选择/构建sql查询，以便仅选择那些将在1天内到期的列表

，而不是其他列表，以避免多个电子邮件警报而不是一次性电子邮件警报我

尝试按照sql查询来完成此任务（我对此查询完全感到困惑）

SELECT car_id FROM cars WHERE expiry_date > DATE_SUB(NOW(), INTERVAL 7 DAY) AND expiry_date < DATE_SUB(NOW(), INTERVAL 1 DAY)

任务 3 - 删除超过 7 天前过期的所有商品

我尝试对此任务进行以下 sql 查询

SELECT car_id FROM cars WHERE expiry_date < DATE_SUB(NOW(), INTERVAL 7 DAY)

请帮助我完善所有 3 个查询，以便 cron 完全按照我的意愿工作。也请让我在哪里必须>=（大于或等于）或<=（小于或等于）

这是sqlfiddle表结构和几条记录（尽管它们尚未过期）

http://sqlfiddle.com/#!2/cfcdf

我将非常感谢您的帮助。

Answer 1

这是你要找的吗？请尝试添加另一列以查看expiry_date和current date time之间的差异，以便更好地了解您正在处理的日期。请查看 MYSQL 中的一些日期函数。

SQLFIDDLE 演示

-- 3rd query expiry dates older than 7 days from 
-- today
SELECT car_id, expiry_Date, 
DATE_sub(NOW(), INTERVAL 7 DAY)
FROM cars 
WHERE expiry_date <= 
DATE_sub(NOW(), INTERVAL 7 DAY)
;
-- same
SELECT car_id, expiry_Date, 
DATE_ADD(NOW(), INTERVAL -7 DAY)
FROM cars 
WHERE expiry_date <= 
DATE_ADD(NOW(), INTERVAL -7 DAY)
;

-- 2nd query going to expire in exactly 1 day
SELECT car_id, expiry_date, 
Now() + interval 1 day
FROM cars 
WHERE expiry_Date = Now() + interval 1 day
;
-- 1st query: expired 
SELECT car_id FROM cars 
WHERE expiry_date < Now()
;
-- 1st query: expired last 24 hours
SELECT car_id,DATEDIFF(expiry_date, Now())
FROM cars 
WHERE expiry_Date < Now()
AND expiry_Date >= Now() - interval 1 day
;

Answer 2

查看这些查询

select * from cars where datediff(EXPIRY_DATE,now())=-1;

select * from cars where 
datediff(DATE_ADD(EXPIRY_DATE, interval 24 hour),now())>=1 and
datediff(DATE_ADD(EXPIRY_DATE, interval 24 hour),now()) <=2;

select * from cars where datediff(expiry_date,now())<=-7;

OPE 他们正在根据您的需要工作。

小提琴 http://sqlfiddle.com/#!2/785ea/5

Answer 3

您的查询没有任何重大错误，如果您不了解所使用的功能，请谷歌它们并阅读它们，直到您这样做。

您的方法存在一个基本问题，因为它依赖于 cron 作业以恰好 24 小时的内段运行 - 到毫秒 - 否则会出现双重和/或遗漏。

您需要另一个表来存储批处理程序上次运行时间的详细信息;用 1 行和过去很长时间的日期开始它，以便我们有一个起点。

您可以按SELECT MAX(date_ran) FROM BatchRecordTables获取最新的批次。将其存储在局部变量 T0 中。获取当前时间，将其存储在局部变量 T1 中（不要在多个查询中使用 NOW（），因为它们的时间会略有不同，并且您需要它们相同）。我不知道这个语法是MySQL - 你必须查找它。

然后你的情况就变成了。

1. 向自上次运行 cron 作业以来列表已过期的人发送电子邮件，即 SELECT car_id FROM cars WHERE Expiry_Date BETWEEN T0 AND T1 .这只会选择在此批次和上一批之间已过期的人。
2. 对于第二种情况，我们需要知道这些人已经收到了第一封电子邮件，即他们的列表在最后一批运行之前已经过期，所以SELECT car_id FROM cars WHERE Expiry_Date BETWEEN DATE_SUB(T1, INTERVAL 6 DAY) AND T0 .这只会选择在最后一批（即他们收到过期的电子邮件）之前和超过 6 天前
过期的人。
3. 同样的 logi 适用 - 我们想知道他们收到了第二封电子邮件。 SELECT car_id FROM cars WHERE Expiry_Date BETWEEN DATE_SUB(T1, INTERVAL 7 DAY) AND DATE_SUB(T0, INTERVAL 6 DAY)

我还建议您不要永久删除列表，而是将它们复制到已删除列表表或使用已删除列标记它们 - 每个都有自己的优点和缺点。在信息时代，永远不要丢弃数据 - 你永远不知道它什么时候可能更有价值。