如何在PostgreSQL中查询同一分钟时间的所有数据？

你当前的查询仅匹配了精确到秒或更细粒度的最大datetime值，所以只能返回单行记录。要获取同一分钟内的所有告警数据，核心思路是将datetime字段截断到分钟级别，再匹配最新分钟对应的所有条目。

不同数据库的实现方案 #

MySQL/MariaDB #

可以用DATE_FORMAT格式化时间，或DATE_TRUNC（MySQL 8.0+支持）直接截断到分钟：

-- 方案1：用DATE_FORMAT
SELECT *
FROM alrm
WHERE ble = $bleAddress
  AND DATE_FORMAT(datetime, '%Y-%m-%d %H:%i') = (
    SELECT DATE_FORMAT(max(datetime), '%Y-%m-%d %H:%i')
    FROM alrm
    WHERE ble = $bleAddress -- 必须加当前设备过滤，确保取的是该设备的最新分钟
  );

-- 方案2：用DATE_TRUNC（MySQL8.0+）
SELECT *
FROM alrm
WHERE ble = $bleAddress
  AND DATE_TRUNC('minute', datetime) = (
    SELECT DATE_TRUNC('minute', max(datetime))
    FROM alrm
    WHERE ble = $bleAddress
  );

PostgreSQL #

直接使用DATE_TRUNC函数处理时间：

SELECT *
FROM alrm
WHERE ble = $bleAddress
  AND DATE_TRUNC('minute', datetime) = (
    SELECT DATE_TRUNC('minute', max(datetime))
    FROM alrm
    WHERE ble = $bleAddress
  );

SQL Server #

通过DATEADD+DATEDIFF组合截断到分钟：

SELECT *
FROM alrm
WHERE ble = $bleAddress
  AND DATEADD(minute, DATEDIFF(minute, 0, datetime), 0) = (
    SELECT DATEADD(minute, DATEDIFF(minute, 0, max(datetime)), 0)
    FROM alrm
    WHERE ble = $bleAddress
  );

重要提示 #

• 子查询中务必保留ble = $bleAddress过滤条件，否则会拿到全表的最新分钟数据，而非当前设备的目标数据。
• 如果datetime字段包含时区信息，需确保时区处理一致，避免截断后出现时间偏差。

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