MySQL时间范围获取指定数量间隔记录（均值计算）及2038问题规避

针对你在内存有限的Arduino设备上展示温度数据图表的需求，以下是完全避开32位时间戳2038问题的单次查询方案，支持按任意时间间隔聚合均值或选取间隔内最接近的样本：

1. 按自然时间周期分组（推荐） #

直接利用数据库的日期截断函数分组，完全不依赖32位时间戳：

按天聚合（对应周度图表7条日均值的需求） #

SELECT 
  AVG(`sVal`) AS daily_avg_temp,
  DATE(`timestamp`) AS group_date
FROM `Test` 
WHERE sKey='U1'  
  AND `timestamp` BETWEEN '2023-08-01 00:00:00' AND '2023-08-30 23:59:00'
GROUP BY DATE(`timestamp`)
ORDER BY group_date;

DATE()函数直接提取日期部分作为分组依据，彻底规避2038溢出问题。

按分钟聚合（对应过去1小时每分钟均值的需求） #

SELECT 
  AVG(`sVal`) AS minute_avg_temp,
  DATE_FORMAT(`timestamp`, '%Y-%m-%d %H:%i:00') AS group_minute
FROM `Test` 
WHERE sKey='U1'  
  AND `timestamp` BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
GROUP BY DATE_FORMAT(`timestamp`, '%Y-%m-%d %H:%i:00')
ORDER BY group_minute;

DATE_FORMAT将时间戳截断到分钟级别，分组逻辑清晰且无时间戳溢出风险。

2. 自定义任意时间间隔聚合 #

如果需要非标准间隔（比如每15分钟、每3小时），可以用日期算术实现分组：

示例：每15分钟聚合一次 #

SELECT 
  AVG(`sVal`) AS interval_avg_temp,
  TIMESTAMPADD(MINUTE, 
    FLOOR(TIMESTAMPDIFF(MINUTE, '2000-01-01', `timestamp`) / 15) * 15,
    '2000-01-01'
  ) AS group_interval
FROM `Test` 
WHERE sKey='U1'  
  AND `timestamp` BETWEEN '2023-08-01 00:00:00' AND '2023-08-01 01:00:00'
GROUP BY group_interval
ORDER BY group_interval;

通过计算目标时间与固定起始时间的分钟差，取整后还原为时间戳，全程使用日期函数操作，无32位时间戳限制。

3. 获取间隔内最接近的样本（替代均值） #

若不需要均值，想要每个时间间隔内最接近中点的样本（适用于MySQL 8.0+、PostgreSQL等支持窗口函数的数据库）：

按天取最接近当天中午的样本 #

WITH daily_groups AS (
  SELECT 
    `sVal`,
    `timestamp`,
    DATE(`timestamp`) AS group_date,
    ABS(TIMESTAMPDIFF(SECOND, `timestamp`, CONCAT(DATE(`timestamp`), ' 12:00:00'))) AS time_diff
  FROM `Test` 
  WHERE sKey='U1'  
    AND `timestamp` BETWEEN '2023-08-01 00:00:00' AND '2023-08-30 23:59:00'
)
SELECT 
  `sVal`,
  `timestamp`,
  group_date
FROM daily_groups
WHERE (group_date, time_diff) IN (
  SELECT group_date, MIN(time_diff) 
  FROM daily_groups 
  GROUP BY group_date
)
ORDER BY group_date;

先计算每个样本到当天中午的时间差，再筛选出每个日期中时间差最小的样本。

原方案的2038问题说明 #

UNIX_TIMESTAMP()返回32位整数类型时间戳，在2038年1月19日之后会发生数值溢出，导致日期计算完全错误。上述所有方案仅使用数据库原生日期/时间函数，彻底避开了这一风险。

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