API日期过滤器时区处理最佳实践及存算方案咨询

问题结论与方案建议 #

新增当地日期字段方案的可行性与弊端

该方案在当前固定业务场景下可以实现需求，但属于性价比很低的折中选择，存在以下明确问题：

• 数据一致性风险：属于冗余存储，你已经在timestamp with time zone字段中存储了完整的时间信息，额外存储的当地日期在时区规则调整（比如夏令时政策变更）、用户时区修改时，会出现和原始时间戳不一致的问题，修复脏数据的成本很高
• 扩展性不足：如果后续业务支持用户跨时区查询历史数据、或者允许用户切换账号时区查看报告，固定存储的单一时区当地日期就无法适配新的需求
• 维护成本提升：后续所有涉及时间写入、更新的逻辑都需要同时维护两套时间字段，很容易出现逻辑疏漏

更优的实现方案

不需要新增字段，直接用PostgreSQL原生的时区转换能力就能实现兼顾开发者体验的筛选逻辑，实现成本远低于维护冗余字段：

• API层同时支持两类筛选参数：仅日期片段（如2021-08-24）、带时区偏移/UTC格式的完整datetime
• 当传入参数为仅日期片段时，要求调用方同步传入timezone参数（无传参默认用UTC），直接用PostgreSQL的时区转换语法查询，该写法可以命中event_time字段的索引，没有性能损耗：

  SELECT * FROM events
  WHERE event_time AT TIME ZONE $1 >= $2::date
    AND event_time AT TIME ZONE $1 < ($2::date + INTERVAL '1 day')
  -- $1为传入的用户时区，如'America/Sao_Paulo'，$2为传入的日期参数
  

• 当传入参数为带时区偏移的完整datetime（如2021-08-24T00:00:00-03:00）或UTC格式datetime（如2021-08-24T03:00:00Z）时，直接用参数值查询即可，不需要额外转换

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