答案

在 SQL 中，有许多函数用于处理日期和间隔：DATEDIFF、DATEADD（T-SQL），以及工作于 INTERVAL 类型（PostgreSQL），还包括将时间转换为不同区域的功能（AT TIME ZONE）。要正确处理日期，关键是使用考虑时区的数据类型（TIMESTAMP WITH TIME ZONE、timestamptz），而不是简单的 DATE 或 DATETIME，尤其是在全球服务中。

在不同的数据库中，语法和工作特点各不相同：

• SQL Server：DATEDIFF、DATEADD、FORMAT，但没有完整的 INTERVAL。时区需要明确设置。
• PostgreSQL：支持 INTERVAL，大量函数（+/- INTERVAL），直接管理时间区域。

错误常常由于 UTC/本地时间存储不一致和混合操作而不考虑差异而产生。

计算差异的例子（PostgreSQL）

SELECT
  EXTRACT(EPOCH FROM (event_end AT TIME ZONE 'UTC' - event_start AT TIME ZONE 'UTC')) / 3600 AS hours
FROM events

有陷阱的问题

DATEDIFF 的日期差异是否会返回负数，这些日期的顺序意味着什么？

答案：是的，DATEDIFF（DATEDIFF(day, d1, d2)）如果 d2 < d1 将返回负数。重要的是不要混淆参数的顺序：首先是测量单位，然后是开始日期，最后是结束日期。

例子：

SELECT DATEDIFF(day, '2024-05-01', '2024-04-25') -- 返回 -6

真实错误的示例

---

故事

在一个有全球用户的项目中，报告是根据每个区域服务器的本地时间构建的。结果是汇总在日期上出现偏差，并且在不同的时区用户中出现了跨天的“漏洞”。已通过将所有时间数据转换为 UTC 并仅在展示时重新计算来修正。

---

故事

通过 DATEDIFF 计算订阅持续时间时，混淆了日期的顺序。结果导致一些用户被错误地视为“超用户”，具有明显的负时间。已通过引入对日期顺序的检查进行修正。

---

故事

开发人员在不考虑时区的情况下存储 DATETIME，在迁移到 PostgreSQL 时，值被解释为 UTC，尽管最初是在 GMT+3，这导致报告中的事件偏移了 3 小时。结论是：始终按时区存储和转换日期！