Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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) You need to improve your accept rate. 2) What have you tried? What have you read? Show code that didn't work the way you thought it should. –  Peter Rowell Dec 7 '12 at 17:29
1  
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 code.google.com/p/noda-time and joda-time.sourceforge.net 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
add comment

1 Answer

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
add comment

Your Answer

 
discard

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.