PHP如何从当前日期获取过去7周、7个月日期范围并实现分组销售统计

核心逻辑和你现有7天统计逻辑对齐，先计算对应时间范围，再调整分组规则即可，分为7周、7个月两个场景实现：

过去7周统计 #

PHP时间范围计算 #

$today = date("Y-m-d");
// 计算7周前的日期作为查询起始点
$lastSevenWeeksStart = date('Y-m-d', strtotime('-7 weeks'));

统计SQL #

注：WEEK函数第二个参数1代表周起始为周一，如果需要匹配示例的周日为周起始，改为0即可

SELECT
    -- 格式化输出周区间，如 08/08/2021 - 08/14/2021
    CONCAT(
        DATE_FORMAT(DATE_SUB(payment.createdDateTime, INTERVAL (WEEKDAY(payment.createdDateTime)) DAY), '%m/%d/%Y'),
        ' - ',
        DATE_FORMAT(DATE_ADD(payment.createdDateTime, INTERVAL (6 - WEEKDAY(payment.createdDateTime)) DAY), '%m/%d/%Y')
    ) AS week_range,
    ROUND(SUM(IFNULL(payment.tmpNetTotal, 0)), 2) AS sales
FROM
    srp_erp_pos_menusalesmaster AS sales
    JOIN (
        SELECT 
            SUM(IFNULL(amount, 0)) AS tmpNetTotal,
            createdDateTime,
            menuSalesID 
        FROM srp_erp_pos_menusalespayments 
        WHERE createdDateTime BETWEEN '{$lastSevenWeeksStart}' AND '{$today}'  
        GROUP BY menuSalesID
    ) AS payment ON sales.menuSalesID = payment.menuSalesID 
WHERE 
    isHold = 0 
    AND sales.isVoid = 0 
    AND companyID = {$companyID} 
GROUP BY YEAR(payment.createdDateTime), WEEK(payment.createdDateTime, 1)
ORDER BY payment.createdDateTime DESC

过去7个月统计 #

PHP时间范围计算 #

$today = date("Y-m-d");
// 计算7个月前的当月第一天作为查询起始点，保证完整统计整月数据
$lastSevenMonthsStart = date('Y-m-d', strtotime('-7 months', strtotime(date('Y-m-01'))));

统计SQL #

SELECT
    -- 格式化输出月区间，如 07/01/2021 - 07/31/2021
    CONCAT(
        DATE_FORMAT(payment.createdDateTime, '%m/01/%Y'),
        ' - ',
        DATE_FORMAT(LAST_DAY(payment.createdDateTime), '%m/%d/%Y')
    ) AS month_range,
    ROUND(SUM(IFNULL(payment.tmpNetTotal, 0)), 2) AS sales
FROM
    srp_erp_pos_menusalesmaster AS sales
    JOIN (
        SELECT 
            SUM(IFNULL(amount, 0)) AS tmpNetTotal,
            createdDateTime,
            menuSalesID 
        FROM srp_erp_pos_menusalespayments 
        WHERE createdDateTime BETWEEN '{$lastSevenMonthsStart}' AND '{$today}'  
        GROUP BY menuSalesID
    ) AS payment ON sales.menuSalesID = payment.menuSalesID 
WHERE 
    isHold = 0 
    AND sales.isVoid = 0 
    AND companyID = {$companyID} 
GROUP BY YEAR(payment.createdDateTime), MONTH(payment.createdDateTime)
ORDER BY payment.createdDateTime DESC

补充说明 #

• 上述SQL基于MySQL语法，若使用其他数据库，仅需替换对应日期处理函数即可
• 分组时同时按年+周/年+月分组，可避免跨年数据统计错误

内容的提问来源于stack exchange，提问作者khn Rzk