問題描述
我有一個包含 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.
這篇關于如何修改現有表以添加時區的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！