本文介绍如何利用 MySQL 的 TIMESTAMPDIFF 与 FROM_UNIXTIME 函数，结合 PHP PDO，精准统计近7天内每天的独立页面访问次数，并生成 CanvasJS 图表所需的数据结构。
本文介绍如何利用 mysql 的 `timestampdiff` 与 `from_unixtime` 函数，结合 php pdo，精准统计近7天内每天的独立页面访问次数，并生成 canvasjs 图表所需的数据结构。
在构建后台数据看板（如管理员页面浏览量统计面板）时，一个常见需求是：按自然日粒度，统计过去7天中每一天的用户访问次数。你的数据库中存储的是 Unix 时间戳（如 Date 字段为 INT(10) 类型），而非 DATETIME 类型——这决定了我们不能直接对时间戳做日期运算，而需先将其转换为 MySQL 可识别的日期格式。
✅ 推荐方案：让数据库完成核心计算（高效且准确）
与其在 PHP 中遍历全部记录并用 foreach + 手动时间比较（易出错、性能差），不如将时间范围判断、分组统计等逻辑下推至数据库层。MySQL 提供了两个关键函数：
-
FROM_UNIXTIME(timestamp)：将 Unix 时间戳转为YYYY-MM-DD HH:MM:SS格式； -
TIMESTAMPDIFF(DAY, start_date, end_date)：计算两个日期之间的天数差（向下取整）。
以下是一条优化后的 SQL 查询，用于获取过去7天内所有访问记录，并附带其距今日的天数差：
SELECT *, TIMESTAMPDIFF(DAY, FROM_UNIXTIME(`Date`), CURDATE()) AS diff FROM new_user_last WHERE FROM_UNIXTIME(`Date`) >= CURDATE() - INTERVAL 7 DAY ORDER BY `Date`;
该查询返回所有发生在 今天 - 7天（含）之后的记录，并新增 diff 字段：
-
diff = 0→ 今日（CURDATE()） -
diff = 1→ 昨日 -
diff = 2→ 前日 - ……
-
diff = 6→ 7天前（即CURDATE() - INTERVAL 6 DAY到CURDATE() - INTERVAL 7 DAY + 1 SECOND区间）
⚠️ 注意：
CURDATE() - INTERVAL 7 DAY表示7天前的 00:00:00，因此该 WHERE 条件实际覆盖的是[7天前 00:00:00, 今日 23:59:59]范围，符合“近7天”的业务语义。
? 统计每日访问量：避免多次查询（最佳实践）
原解决方案中执行了7次独立查询（INTERVAL 1 DAY ~ INTERVAL 7 DAY），虽可行但效率低下。更优做法是单次查询 + PHP 数组聚合：
// 1. 单次获取近7天全部有效记录（含 diff）
$stmt = $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`'
);
$stmt->execute();
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);
// 2. 初始化7天计数器（索引0=今天，6=7天前）
$counts = array_fill(0, 7, 0);
// 3. 遍历并累加（diff 值即为数组索引）
foreach ($records as $row) {
$daysAgo = (int)$row['diff'];
if ($daysAgo >= 0 && $daysAgo < 7) {
$counts[$daysAgo]++;
}
}
// 4. 构建 CanvasJS 兼容的数据点（倒序：7天前→今日）
$dataPoints = [];
$labels = ['7天前', '6天前', '5天前', '4天前', '3天前', '2天前', '今日'];
for ($i = 6; $i >= 0; $i--) {
$dataPoints[] = [
"y" => $counts[$i],
"label" => $labels[$i]
];
}✅ 优势：
- 仅1次数据库查询，大幅降低 I/O 与连接开销；
- 逻辑清晰，避免嵌套查询与重复计算；
-
天然支持动态扩展（如改为30天，仅需调整
array_fill和循环范围）； -
diff值直接对应自然日偏移，无需手动维护$FoundXDaysAgo等易错变量。
? 补充说明与注意事项
-
时区一致性：确保 PHP 的
date_default_timezone_set()与 MySQL 的time_zone设置一致（推荐统一为Asia/Shanghai），否则CURDATE()与FROM_UNIXTIME()结果可能偏差。 -
索引优化：为
Date字段添加 B-tree 索引（ALTER TABLE new_user_last ADD INDEX idx_date (Date);），可显著提升WHERE FROM_UNIXTIME(\Date`) >= ...` 的查询速度。 -
大数据量场景：若日志量极大（百万+），建议改用
DATE(FROM_UNIXTIME(\Date`))` 配合函数索引（MySQL 8.0+），或预聚合到汇总表。 -
CanvasJS 渲染：最终
$dataPoints可直接json_encode()输出至前端，配合官方 Spline Chart 示例快速渲染图表。
通过将时间计算交由数据库处理，并采用单次查询 + 内存聚合的方式，你不仅能获得准确、可读性强的每日访问统计，还能为后续扩展（如按小时、用户来源维度分析）打下坚实基础。
