php - 如何查询上周周四到周三？

Question

我正在尝试获取一个查询，以提取上周从周四上午 12:00 到周三晚上 12:00 的记录。

这需要始终运行并保持最新状态。我对本周从周四到周三还有另一个查询，它实际上有效。我只需要另一个查询来显示上周过去一周后的结果。

我相信此查询是针对上周的，但不是从星期四开始并在星期三结束。

 SELECT collector, SUM('amountcollected') as 'totalsum' FROM commissioninfo 
   WHERE thedate >= Curdate() - INTERVAL Dayofweek(Curdate())+6 day 
   AND thedate < Curdate() - INTERVAL Dayofweek(Curdate())-1 day  
   GROUP BY collector ORDER BY totalsum DESC

Answer 1

使用MySQL自己的日期函数可能有更好的方法来做到这一点，但我想我可能会这样做。我假设您指的是周三晚上 11:59:59，而不是中午:

$lastThursday = new DateTime("上周星期四");

$wednesday = new DateTime("星期三");

$today = new DateTime("now");

$day = $today->format("l");

if ($day == "Sunday" || $day == "Monday" || $day == "Tuesday" || $day == "Wednesday") {
    $lastThursday->modify("-1 week");
    $wednesday->modify("-1 week");
}

$query = "SELECT Collector, SUM('amountcollected') as 'totalsum' FROM Commissioninfo WHERE BETWEEN ".$lastThursday->format("Y-m-d")."AND ".$wednesday->format (“Y-m-d”)。“GROUP BY 收集器 ORDER BY 总和 DESC”；