Sign up ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

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?

share|improve this question
1) There you go, 2) I'm more-so trying to make sure I'm doing this right, and checking if SQL has this capability. – Jerry Dodge Dec 7 '12 at 17:33
This is entirely not on topic for Programmers. We need both what you have attempted. I suggest looking at and to get an idea how to convert from Time Zone A to Time Zone B the correct way. I already flagged this to be migrated. – Ramhound Dec 7 '12 at 17:59

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
Looking back at my question and your answer (while I never got around to doing this after 2 years), I see there's one critical point that should be made. When initializing a client app, it should check the local time zone, compare to server's time zone, and measure the amount of time of difference (integer number of minutes should be fine). Then, on the communication layer (between the app logic and server connection) should be another layer of date/time translation. No matter the language being used, this is a fundamental. That's the key to ensuring date/time sent to/from server persists. – Jerry Dodge Dec 2 '14 at 0:51
Ironically, I just did a search on the subject, and found this question I asked 2 years ago and forgot about :-) – Jerry Dodge Dec 2 '14 at 0:52
I get the feeling that you gave the company an estimate to make their legacy system timezone aware and they decided against it. Now 2 years later these problems are still annoying them and they are hoping for a less costly answer this time :) I know the pain. – maple_shaft Dec 2 '14 at 2:18
Well, I never gave a definite estimate, but made it clear that our software really needs it. It was originally assuming just a single location in its legacy times. Since then, multi-location was introduced. But locale was never considered. Just the server's date/time, with the future hopes of one day converting everything. – Jerry Dodge Dec 2 '14 at 2:35
In the end, this task still sits in the queue of "to-do's". – Jerry Dodge Dec 2 '14 at 2:47

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.