How to modify existing tables for timezone additio

问题:

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.

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)

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?

My ideas were to:

1. for each date/time column in a table, add two extra columns (for tz-name & offset)
   • this feels like bad design
2. add one table with columns pk, TABLE_NAME, COL_NAME, ROW_PK, TZ_NAME, TZ_OFFSET
   • although more portable, this table will contain millions of rows evetually, since it is the whole database's dates crammed into one table
3. add a new table for each existing table (one-to-one) with tz-names, and offsets for each date column
   • although not portable, this seems like the best (relational) option

Does anyone have any other ideas or best-practices?

回答1:

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.