Legacy Systems – How to Make a System Time-Zone Sensitive

delphitime

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?

Best Answer

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.

Related Solutions

Data Types – Is Integer Overused as a Data Type?

One reason why I don't use unsigned integer types all that much in Delphi is that they can create problems when mixed with signed integers. Here's one that bit me once:

for i := 0 to List.Count - 1 do
  //do something here

I had i declared as an unsigned integer, (after all, it's an index into a list that starts at 0, it never has to be negative, right?), but when List.Count was 0, it would not short-circuit the loop as expected because 0 - 1 evaluates to a really high positive number. Oops!

Between the potential safety problems inherent in mixing signed and unsigned integers, and the range issues, (if you're going to need positive numbers larger than high(signed whatever), it's quite likely that you'll also end up needing positive numbers larger than high(unsigned whatever) too, so moving up to the next larger size instead of switching from signed to unsigned of the same size is usually the correct action,) I really haven't found too many uses for unsigned integers when representing most data.

C# – How should I deal with Time Zones in a .NET WCF application

I think you're on the right track with using the built in TimeZoneInfo class. The ID should never change, but the name will (I learned that lesson the hard way when several changed between xp and vista). I have used a virtually identical approach (UTC only at the service and db layers, local time on the web server & client) and it worked just fine.

One thing I suggest is to wrap the various TimeZoneInfo methods you intend to use with an interface ITimeZoneConverter or something of that nature, that way if you need to change your implementation later, it should be easier to do.

Random Annoyance: The .Net TimeZoneInfo class can't give you an abbreviation given a timezone. IE: No "EST" for "Eastern Standard Time", so if you need that, plan on having a lookup into some sort of resource file or table for that.

Javascript won't actually be able to tell you the timezone, just the timezone offset from UTC. It's a subtle difference, and in your case it sounds like it won't matter, but it's something to keep in mind when looking at what timezones get assigned to your users.

I have seen some JS libraries such as this one which attempt to guess the timezone on the client side based on offset and most populated timezones. It is an interesting approach, but I don't know how well it would work in production.

Finally, one thing that bit me in the past was that JSON doesn't actually specify anything about how to format datetimes. (related reading) If you're doing a lot of ajax/async requests and intend to convert back and forth between utc & local times, this can be painful. Make sure you write some good common parsing/offsetting functions and make sure everyone on your team is aware of them and doesn't re-invent the wheel every time when dealing with these issues.