在PostgreSQL中存储时间与时区数据的最佳实践

针对用户配置每日更新时段与时区的存储需求，推荐以下无需代码清洗数据的方案：

1. 用IANA时区标识符存储时区，数据库层面做合法性约束 #

放弃EST这类模糊的时区缩写，改用IANA标准时区标识符（如America/New_York、Asia/Shanghai），这类标识符包含完整的夏令时规则，是目前时区处理的行业标准。

在数据库层面直接约束时区字段的合法性，从源头避免非法值进入：

• PostgreSQL：利用内置的pg_timezone_names系统表做CHECK约束，确保存储的时区是有效IANA时区：

  CREATE TABLE user_notification_settings (
      user_id INT PRIMARY KEY,
      -- 其他业务字段
      timezone TEXT CHECK (timezone IN (SELECT name FROM pg_timezone_names))
  );
  

• MySQL：可通过自定义CHECK约束，或预先导入合法IANA时区列表并做外键关联，确保输入时区合法。

这种方式无需在代码中做清洗校验，所有合法性判断由数据库完成。

2. 用整数存储每日时段，替代time类型 #

不用数据库的time类型存储时段，而是存储当日0点开始的分钟数（范围0-1439，比如9:00对应540，18:30对应1110），用integer类型存储，并添加CHECK约束限制值范围：

ALTER TABLE user_notification_settings
ADD COLUMN daily_notification_minutes INT CHECK (daily_notification_minutes BETWEEN 0 AND 1439);

为什么选这种方式？ #

• 避免time类型带来的时区混淆问题，整数存储的是纯时段偏移，无时区关联；
• 数据库约束直接保证时段值合法，无需代码校验；
• 代码层转换简单：将整数分钟数直接转换为时分格式，结合用户时区就能计算出准确的通知时间。

3. 业务代码中的时间计算逻辑 #

当需要计算用户的下一次通知时间时：

1. 从数据库取出用户的daily_notification_minutes和timezone；
2. 用时区标识符初始化时区对象（比如Python的zoneinfo.ZoneInfo(timezone)，Java的ZoneId.of(timezone)）；
3. 获取当前时间并转换到用户时区，将时分调整为对应分钟数的时段，再转换回UTC时间用于调度或存储。

这种方案全程无需代码清洗数据，所有合法性校验由数据库完成，同时保证了时区计算的准确性。

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