How to make a legacy system time-zone sensitive?

I need to implement time zones in a very large and old Delphi system, where there’s a central SQL Server database and possibly hundreds of client installations around the world in different time zones. The application already interacts with the database by only using the date/time of the database server. So, all the time stamps saved in both the database and on the client machines are the date/time of the database server when it happened, never the time of the client machine.

So, when a client is about to display the date/time of something (such as a transaction) which is coming from this database, it needs to show the date/time converted to the local time zone. This is where I get lost.

I would naturally assume there should be something in SQL to recognize the time zone and convert a DateTime field dynamically. I’m not sure if such a thing exists though. If so, that would be perfect, but if not, I need to figure out another way.

This Delphi system (multiple projects) utilizes the SQL Server database using ADO components, VCL data-aware controls, and QuickReports (using data sources). So, there’s many places where the data goes directly from the database query to rendering on the screen, without any code to actually put this data on the screen. In the end, I need to know when and how should I get the properly converted time?

What is the proper way to ensure that I handle Dates and Times correctly in a legacy application?

Dates and timezones are tricky things, and generally no matter what kind of technology stack you are working on there is rarely a turn key solution.

Most databases will store a date or timestamp as little more than the exact date or timestamp that was given to it. When talking with a database through queries they are generally under the assumption that all dates and timestamps represent a point in time under the same time zone. In other words, if I write a query to see if Aug 2nd 2012 4:10pm is before the same date at 5:10pm then that will evaluate to true returning me that record. Suppose the record was saved as 4:10pm Eastern Standard Time when my database server is in Pacific Standard Time. The database is time zone agnostic and now my query is not returning the data I expect.

Convert all application dates and times to UTC or GMT before persisting

Languages like Java, Javascript and C# treat dates and times a bit differently. They will generally measure time as a number of milliseconds from a given universal point in time. This is the universal time as that specific number of milliseconds from 0 represents a number of different dates or times in different timezones at any given point. Most of these languages generally have a solid Date and Time API or a good third party library can be found that make viewing this millisecond count in a number of timezones as painless as possible.

Unless otherwise specified, if I use a standard data access API in a common language, most will take a date object and convert it into the date and time of the servers default timezone in the specified database format. Figure out how your language data access API and database stores dates and how it interprets them from the database and try to store your dates at the database level in GMT. Likewise when querying make sure that Date objects or variables in your language are representing a millisecond count that is equivalent to the GMT date time stored in the database.

Persist Time Zones for Dates that are used in Business Logic

What I mean by this is that perhaps if you have a Modified Date column in your database, then it may not be important to business logic, however a column Appointment Date probably will be important.

Somewhere in your schema you need to determine where locale specific data is stored and store the Time Zone to define that locale in the appropriate parent table. Your business logic that is considering dates, or your presentation logic that is preparing dates for display must be able to fetch the appropriate Time Zone so that when I am comparing 4:10pm to 5:10pm, that I am comparing dates from the appropriate universal time. 4:10pm EST and 4:10pm PST are seperate points in universal time. Again a good Date and Time API or third party library makes date comparisons easy, when you understand the underlying nature of how dates work in that programming language.

Business Logic Should be refactored to be time zone aware.

Presentation Logic Should be refactored to present a display value that is appropriately displaying the correct data per the appropriate given Time Zone.

One final thought to consider when performing this work is to write exhaustive unit tests that thoroughly exhaust many types of Date and Time combinations to make sure that all business logic works as expected.

The problem here is that the views are connected directly to the models.

Although this sounds horrible to achieve in delphi, the first step is to enforce a separation of concerns.

You could use a MVVM approach and have intermediary view-models that perform localised transformation of database values for display. These same view-models can transform localised input into the timezone the database expects when storing data.

MVP or some other MV* variant might be better suited depending on how you’re currently set up and your exact requirements, but you get the picture. The upside of all this work is that future localised changes will be much, much easier to accomplish.

Filed under: softwareengineering - @ 14:20

Thẻ: delphi, time

Thiết kế website giá rẻ

Danh mục

How to make a legacy system time-zone sensitive?

Convert all application dates and times to UTC or GMT before persisting

Persist Time Zones for Dates that are used in Business Logic