如何修改现有表以添加时区
我有一个包含 500 多个表的大型应用程序，我必须将应用程序转换为时区感知(当前应用程序使用 new java.util.Date(), GETDATE() 与服务器的时区).即不支持任何时区.
I have a large application with 500+ tables, I have to convert the application to be timezone aware (currently application uses new java.util.Date(), GETDATE() with server's timezone). i.e. no timezone support whatsoever.
为了简化开发，我已将此任务分为几个步骤，我确定的第一个步骤是根据服务器的时区将所有旧日期更改为 UTC.(主要位于一个时区，所以这是我最好的猜测)
I have organised this task into a few steps so as to ease development, my first identified step is to change all old dates to UTC based on the server's timezone. (mostly located in one timezone, so this is my best guess)
接下来，我需要修改数据库和应用程序代码以使用时区名称和偏移量以 UTC 格式保存所有日期，这就是我的问题所在...
Next, I need to modify the database and application code to save all dates in UTC with a timezone name and offset, this is where my problem comes in...
我将如何修改数据库/表以很好地支持这一点?
How would i go about modifying the database/tables to support this in a good manner?
我的想法是:
- 对于表中的每个日期/时间列，添加两个额外的列(用于 tz-name 和偏移量)
- 感觉很糟糕的设计
- 虽然更便携，但这张表最终将包含数百万行，因为它是将整个数据库的日期塞进一张表中
- 虽然不可移植，但这似乎是最好的(关系)选项
有人有其他想法或最佳实践吗?
Does anyone have any other ideas or best-practices?
推荐答案
根据我的经验，您通常应该将数据存储为 UTC，并将相关时区放在单独的列中.拥有一张时区表并存储时区键是关系数据库的合理做法.
In my experience you should generally store the data as UTC, with the relevant timezone in a separate column. Having a table for timezones and storing the timezone key is the reasonable thing to do with a relational database.
不过，在这种情况下，您的所有数据都已在当地时间，因此在这种情况下，您可以将当地时间存储在时间列中，并为时区添加一列.这样您就不必转换数据库中已有的日期.
In this case though, all your data is in a local time already, so in this case you can store the local time in the time column, and add a column for the time zone. That way you don't have to convert the dates that are already in the database.
没有必要存储偏移量，除非您最终注意到从日期和区域转换为偏移量太耗时.
Storing offset is not necessary, unless you end up noticing that converting from date and zone to offset is too time-consuming.
相关文章