本文介绍如何利用 MySQL 的 TIMESTAMPDIFF 和 FROM_UNIXTIME 函数，结合 PHP PDO，精准统计过去7天内每天的独立页面访问次数，并为 CanvasJS 图表提供结构化数据。
本文介绍如何利用 mysql 的 `timestampdiff` 和 `from_unixtime` 函数，结合 php pdo，精准统计过去7天内每天的独立页面访问次数，并为 canvasjs 图表提供结构化数据。
在构建后台数据看板时，准确统计“每日页面访问量”是常见需求。假设你已将用户每次页面跳转或刷新的时间戳（Unix 时间戳，单位秒）存入数据库字段 Date（类型为 INT），目标是按自然日（如：今天、昨天、前天…至7天前）分组统计访问次数，并输出为 CanvasJS 可用的 dataPoints 格式。
直接在 PHP 中遍历全部记录并手动比对时间范围（如原代码中通过 $FoundXDaysAgo 计算边界再 if 判断）不仅性能低下，且易出错（尤其跨月/闰年场景）。最佳实践是将时间计算与过滤逻辑下推至数据库层——由 MySQL 完成日期解析、区间判断和聚合，PHP 仅负责结果消费。
✅ 推荐方案：单查询 + 区间差分法（高效、准确、可扩展）
核心思路：不执行7次独立查询，而是一次查询获取最近7天所有记录，并借助 SQL 计算每条记录距今日的天数差 diff，再在 PHP 中按 diff 分组计数：
// 1. 一次性获取最近7天（含今天）的所有访问记录，并计算其距今日的天数差
$Query = $db->prepare('
SELECT
*,
TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff
FROM new_user_last
WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 7 DAY
ORDER BY `Date`
');
$Query->execute();
$records = $Query->fetchAll(PDO::FETCH_ASSOC);
// 2. 初始化7天计数器（索引0=7天前，索引6=今天）
$counts = array_fill(0, 7, 0); // [0,0,0,0,0,0,0]
// 3. 遍历结果，按diff值累加计数（diff=0→今天，diff=1→昨天...diff=6→7天前）
foreach ($records as $row) {
$diff = (int)$row['diff'];
if ($diff >= 0 && $diff < 7) { // 确保diff在有效范围内
$counts[$diff]++;
}
}
// 4. 构建CanvasJS所需dataPoints（倒序：7天前→今天 → 对应label从左到右）
$labels = ['7天前', '6天前', '5天前', '4天前', '3天前', '2天前', '今天'];
$dataPoints = [];
for ($i = 6; $i >= 0; $i--) { // 从今天（$counts[0]）开始倒序排列，使图表x轴左→右为时间正向
$dataPoints[] = [
'y' => $counts[6 - $i], // $counts[0]→今天，对应label[6]
'label' => $labels[$i]
];
}
// 或更清晰的写法（推荐）：
$dataPoints = [];
for ($dayOffset = 6; $dayOffset >= 0; $dayOffset--) {
$daysAgo = 6 - $dayOffset; // 0→今天, 1→昨天...6→7天前
$dataPoints[] = [
'y' => $counts[$daysAgo],
'label' => $labels[$dayOffset]
];
}⚠️ 关键注意事项
-
时间戳类型适配：务必确认数据库
Date字段存储的是 Unix 时间戳（INT）。若为DATETIME/TIMESTAMP类型，应直接使用TIMESTAMPDIFF(DAY,Date, CURDATE())，无需FROM_UNIXTIME()。 -
时区一致性：
CURDATE()和FROM_UNIXTIME()均依赖 MySQL 服务器时区。确保 PHP 时区（date_default_timezone_set()）与 MySQL 时区一致，避免日期偏差。 -
索引优化：为
Date字段添加索引（CREATE INDEX idx_date ON new_user_last(Date);），大幅提升WHERE FROM_UNIXTIME(Date) >= ...查询性能。 -
避免 N+1 查询：原方案中7次独立查询（
INTERVAL 7 DAY至INTERVAL 1 DAY）会产生显著 I/O 开销。单查询方案将数据库往返次数降至1次，效率提升数倍。 -
数据完整性：
TIMESTAMPDIFF(DAY, ...)返回的是日历天数差（非精确秒数），天然符合“自然日统计”需求，自动处理月末、闰年等边界。
? 输出示例（供 CanvasJS 使用）
var chart = new CanvasJS.Chart("chartContainer", {
data: [{
type: "spline",
dataPoints: <?php echo json_encode($dataPoints); ?>
}]
});
chart.render();最终生成的 $dataPoints 形如：
[ ["y" => 128, "label" => "7天前"], ["y" => 142, "label" => "6天前"], // ... ["y" => 215, "label" => "今天"] ]
此方案兼具高性能、高可读性与强健性，是生产环境统计类需求的推荐实现。
