如何修改现有表以添加时区

Question

>我有一个包含 500+ 表的大型应用程序，我必须将应用程序转换为时区感知（当前应用程序使用 new java.util.Date() ，GETDATE()服务器的时区）。 即没有任何时区支持。

我

将此任务组织为几个步骤以简化开发，我确定的第一步是根据服务器的时区将所有旧日期更改为UTC。（主要位于一个时区，所以这是我最好的猜测）

接下来，我需要修改数据库和应用程序代码，以使用时区名称和偏移量以 UTC 格式保存所有日期，这就是我的问题出现的地方......

我将如何修改数据库/表以良好的方式支持此功能？

我的想法是：

1. 对于表中的每个日期/时间列，添加两个额外的列（用于 tz-name 和偏移量）
   • 这感觉像是糟糕的设计
2. 添加一个包含列的表pk、TABLE_NAME、COL_NAME、ROW_PK、TZ_NAME、TZ_OFFSET
   • 虽然更具可移植性，但此表将包含数百万行，因为它是整个数据库的日期塞进一个表中
3. 为每个现有表（一对一）添加一个新表，其中包含 tz 名称和每个日期列的偏移量
   • 虽然不便携，但这似乎是最好的（关系）选择

有没有人有其他想法或最佳实践？

Answer 1

根据我的经验，您通常应该将数据存储为 UTC，并将相关时区存储在单独的列中。拥有时区表并存储时区密钥是关系数据库的合理做法。

但是，在这种情况下，

所有数据都已采用本地时间，因此在这种情况下，您可以将本地时间存储在时间列中，并为时区添加一列。这样，您就不必转换数据库中已有的日期。

存储偏移量不是必需的，除非您最终注意到从日期和区域转换为偏移量太耗时。