Mysql 查询选择未来 45 天内的所有日期(仅限日和月)

Question

我需要选择未来45天内的所有日期(天和月((不考虑年份(。以下查询已停止工作。就像字面上开始不返回行，当那里肯定有数据时。

有其他方法可以进行此查询吗？如果需要，很乐意检查php循环中的记录。

基本上，我需要在接下来的45天内显示所有记录，即使年份已经过去。

SELECT
p.*,
c.company 
FROM
products p 
LEFT JOIN
customers c 
ON c.id = p.id 
WHERE
DATE_FORMAT(p.service_date, '%m-%d') >= DATE_FORMAT(CURDATE(), '%m-%d') 
AND DATE_FORMAT(p.service_date, '%m-%d') <= DATE_FORMAT((CURDATE() + INTERVAL 45 DAY), '%m-%d') 
AND c.email_service = 1 
ORDER BY
p.service_date ASC

Answer 1

用php 解决了这个问题

// Get the dates m-d for the next 45 days
for ($i = 0; $i <= 45; $i++) {
$days = '+' . $i . ' days';
$date = date('m-d', strtotime($days));
if ($i != 45) {
$DateQuery .= "'$date',";
} else {
$DateQuery .= "'$date'";
}
}

并将我需要的日期直接插入查询

SELECT
p.*,
c.company 
FROM
products p 
LEFT JOIN
customers c 
ON c.id = p.id 
WHERE
DATE_FORMAT(p.service_date, '%m-%d') IN 
(
$DateQuery
)
AND c.email_service = 1 
ORDER BY
p.service_date ASC

Answer 2

我不知道为什么您的原始查询使用列(service_date(上的Date_Format()进行范围比较，该列已经存储在MySQL日期类型中。它也不能使用任何索引(如果已定义(。

您只需在日期上加/减Interval：

SELECT
p.*,
c.company 
FROM
products p 
LEFT JOIN
customers c 
ON c.id = p.id 
WHERE
p.service_date >= CURDATE() 
AND p.service_date <= (CURDATE() + INTERVAL 45 DAY) 
AND c.email_service = 1 
ORDER BY
p.service_date ASC

Answer 3

解决此问题的最佳方法是使用DAYOFYEAR。困难的部分是处理年底后45天内的情况，在这种情况下，你必须拆分支票，看看日期是从现在到年底还是从年初到现在+45天；否则，你只需检查一年中的哪一天是否在现在到现在+45天之间。尝试用以下内容替换您的条件：

CASE WHEN DAYOFYEAR('2018-12-31')-DAYOFYEAR(NOW()) < 45 THEN
DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR('2018-12-31') OR
DAYOFYEAR(p.service_date) BETWEEN 1 AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)
ELSE
DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)
END

您的完整查询应该如下所示：

SELECT
p.*,
c.company 
FROM
products p 
LEFT JOIN
customers c 
ON c.id = p.id 
WHERE
(CASE WHEN DAYOFYEAR('2018-12-31')-DAYOFYEAR(NOW()) < 45 THEN
DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR('2018-12-31') OR
DAYOFYEAR(p.service_date) BETWEEN 1 AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)
ELSE
DAYOFYEAR(p.service_date) BETWEEN DAYOFYEAR(NOW()) AND DAYOFYEAR(NOW() + INTERVAL 45 DAY)
END)
AND c.email_service = 1 
ORDER BY
p.service_date ASC

如果你担心闰年，你可以将DAYOFYEAR('2018-12-31')改为DAYOFYEAR(CONCAT(YEAR(NOW), '-12-31'))