How to modify existing tables to add a time zone

Question

I have a large application with 500 + tables, I need to convert the application to a service area (currently the application uses new java.util.Date() , GETDATE() with the server time zone). that is, no support for the time zone.

I organized this task in several steps to facilitate the development process, my first step was to change all the old dates to UTC based on the server’s time zone. (mostly in one time zone, so this is my best guess)

Then I need to change the database and application code to save all dates in UTC with the name and timezone offset, this is where my problem arises ...

How can I modify the database / tables to maintain this in a good manner?

My ideas were as follows:

• for each date / time column in the table, add two additional columns (for tz-name and offset)
  • It looks like a bad design.
• 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, as these are whole database dates, crammed into one table.
• 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?

Answer 1

In my experience, you should store the data as UTC, with the appropriate time zone in a separate column. Having a table for time zones and saving a time zone key is a reasonable thing to do with a relational database.

In this case, all your data is already in local time, so in this case you can save the local time in a time column and add a column for the time zone. This way you do not need to convert dates that are already in the database.

Saving the offset is not required unless you notice that converting from date and zone to offset is too time consuming.