問(wèn)題描述

我有一個(gè)包含 500 多個(gè)表的大型應(yīng)用程序，我必須將應(yīng)用程序轉(zhuǎn)換為時(shí)區(qū)感知(當(dāng)前應(yīng)用程序使用 new java.util.Date(), GETDATE() 與服務(wù)器的時(shí)區(qū)).即不支持任何時(shí)區(qū).

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.

為了簡(jiǎn)化開(kāi)發(fā)，我已將此任務(wù)分為幾個(gè)步驟，我確定的第一個(gè)步驟是根據(jù)服務(wù)器的時(shí)區(qū)將所有舊日期更改為 UTC.(主要位于一個(gè)時(shí)區(qū)，所以這是我最好的猜測(cè))

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)

接下來(lái)，我需要修改數(shù)據(jù)庫(kù)和應(yīng)用程序代碼以使用時(shí)區(qū)名稱和偏移量以 UTC 格式保存所有日期，這就是我的問(wèn)題所在...

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...

我將如何修改數(shù)據(jù)庫(kù)/表以很好地支持這一點(diǎn)?

How would i go about modifying the database/tables to support this in a good manner?

我的想法是:

1. 對(duì)于表中的每個(gè)日期/時(shí)間列，添加兩個(gè)額外的列(用于 tz-name 和偏移量)
   • 感覺(jué)很糟糕的設(shè)計(jì)

• 雖然更便攜，但這張表最終將包含數(shù)百萬(wàn)行，因?yàn)樗菍⒄麄€(gè)數(shù)據(jù)庫(kù)的日期塞進(jìn)一張表中

• 雖然不可移植，但這似乎是最好的(關(guān)系)選項(xiàng)

有人有其他想法或最佳實(shí)踐嗎?

Does anyone have any other ideas or best-practices?

推薦答案

根據(jù)我的經(jīng)驗(yàn)，您通常應(yīng)該將數(shù)據(jù)存儲(chǔ)為 UTC，并將相關(guān)時(shí)區(qū)放在單獨(dú)的列中.擁有一張時(shí)區(qū)表并存儲(chǔ)時(shí)區(qū)鍵是關(guān)系數(shù)據(jù)庫(kù)的合理做法.

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.

不過(guò)，在這種情況下，您的所有數(shù)據(jù)都已在當(dāng)?shù)貢r(shí)間，因此在這種情況下，您可以將當(dāng)?shù)貢r(shí)間存儲(chǔ)在時(shí)間列中，并為時(shí)區(qū)添加一列.這樣您就不必轉(zhuǎn)換數(shù)據(jù)庫(kù)中已有的日期.

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.

沒(méi)有必要存儲(chǔ)偏移量，除非您最終注意到從日期和區(qū)域轉(zhuǎn)換為偏移量太耗時(shí).

Storing offset is not necessary, unless you end up noticing that converting from date and zone to offset is too time-consuming.

這篇關(guān)于如何修改現(xiàn)有表以添加時(shí)區(qū)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！