SQL查询中如何将UTC时间戳转为当日结束时间（而非加24小时）？

你之前直接给${toTimestamp}加86400000的方式，只有当${fromTimestamp}恰好是当日0点时才会得到当日结束时间。如果${fromTimestamp}是当日任意时刻，加24小时只会得到次日同一时刻，不符合需求。

核心思路是：先把${fromTimestamp}对应的UTC时间截断到当日0点，再计算当日的最后一刻（23:59:59.999，毫秒级），具体实现因数据库方言而异：

1. MySQL（毫秒级时间戳） #

将毫秒时间戳转成日期，取次日0点后减1毫秒，再转回毫秒时间戳：

SELECT *
FROM mytable fact
JOIN time_table time ON (time.time_5_min_utc = fact.event_5_min_utc)
WHERE fact.event_utc >= ${fromTimestamp}
  -- 计算当日结束的毫秒时间戳
  AND fact.event_utc <= UNIX_TIMESTAMP(DATE_ADD(DATE(FROM_UNIXTIME(${fromTimestamp}/1000)), INTERVAL 1 DAY)) * 1000 - 1
  AND time.time_5_min_utc >= ${fromTimestamp} 
  AND time.time_5_min_utc <= UNIX_TIMESTAMP(DATE_ADD(DATE(FROM_UNIXTIME(${fromTimestamp}/1000)), INTERVAL 1 DAY)) * 1000 - 1

如果是秒级时间戳，去掉所有/1000和*1000的部分，直接减1秒即可。

2. PostgreSQL（毫秒级时间戳） #

通过时间戳截断和区间计算得到当日结束时间：

SELECT *
FROM mytable fact
JOIN time_table time ON (time.time_5_min_utc = fact.event_5_min_utc)
WHERE fact.event_utc >= ${fromTimestamp}
  AND fact.event_utc <= EXTRACT(EPOCH FROM (DATE_TRUNC('day', TO_TIMESTAMP(${fromTimestamp}/1000)) + INTERVAL '1 day') - INTERVAL '1 millisecond') * 1000
  AND time.time_5_min_utc >= ${fromTimestamp} 
  AND time.time_5_min_utc <= EXTRACT(EPOCH FROM (DATE_TRUNC('day', TO_TIMESTAMP(${fromTimestamp}/1000)) + INTERVAL '1 day') - INTERVAL '1 millisecond') * 1000

3. BigQuery（毫秒级时间戳） #

利用BigQuery的时间函数链实现：

SELECT *
FROM mytable fact
JOIN time_table time ON (time.time_5_min_utc = fact.event_5_min_utc)
WHERE fact.event_utc >= ${fromTimestamp}
  AND fact.event_utc <= TIMESTAMP_TO_MILLIS(TIMESTAMP_SUB(TIMESTAMP_ADD(DATE(TIMESTAMP_MILLIS(${fromTimestamp})), INTERVAL 1 DAY), INTERVAL 1 MILLISECOND))
  AND time.time_5_min_utc >= ${fromTimestamp} 
  AND time.time_5_min_utc <= TIMESTAMP_TO_MILLIS(TIMESTAMP_SUB(TIMESTAMP_ADD(DATE(TIMESTAMP_MILLIS(${fromTimestamp})), INTERVAL 1 DAY), INTERVAL 1 MILLISECOND))

注意事项 #

• 若你的时间戳是秒级而非毫秒级，需对应调整函数中的单位转换逻辑；
• 条件中使用<=而非<，是为了包含当日最后一刻的时间戳（如23:59:59.999）。

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