Managing Time Zones and location aware times in application?

Question

In my application (Silverlight client - server - SQL Server DB) with SQL Server as a back end I need to make various calculations and display times in various time zones. I need to make calculations on both client, server and DB sides.

This is application where I need various time zones at the same time, it's not typical "Local vs UTC" All data will be stored in UTC format but I will need to make conversions to: 1. Location time 2. User's time

So, let's say if we talking air travel - I will store times in UTC but user will see them in departure/arrival time zones.

I'm looking for best practices and maybe correct me if I'm wrong on how I'm going to approach it:

1. Database will have "TimeZones" table with info like Time zone name, id, time offset and is this zone observes DST
2. Users and locations in database will be linked to TimeZone
3. On client and server (.NET) I'm going to load TimeZones table and cache it inside my shared DateTimeService. This way I will be able to bind UI when needed and do all conversions I need in one central place
4. Calculations on UI/server will be done with .NET's built-in DateTime & TimeZoneInfo functionality

What bothers me is that I will have to do 2 different sets of functionality on SQL Server and .NET for converting. I don't want CLR code in SQL Server. Most likely I will have manually coded time conversion functions in T-SQL and I will have TimeZoneInfo doing work for me on .NET side.

Also, I will have to do FK everywhere in DB where I reference TImeZOne table. That will cause other problems like constantly joining, etc.

How do you manage this kind of information in your databases and applications?

Answer 1

As djacobson mentions, the best idea is to push the conversion out away from your DB. Store the times as UTC in the DB. Require that clients do the timezone converting and only communicate with the server in UTC time in order to simplify your server's tasks. If the server does need to be date-aware of the timezone for some reason, have the client or service pass an offset parameter along with the DT parameter, so the server can use it as a modifier and still work with UTC times. I like to pass the number of minutes needed to adjust the DT parameters to make them hit UTC, but there are other equally valid ways to do it, as long as you ensure consistent handling of requests.

Here's a simplified example from a project I recently worked on, which was a meeting scheduling application targeted for mobile devices. A client in UTC -6 requesting all meetings which start on November 20, 2011 would pass MinutesToAdjustBy = 360 and StartDate = {11/16/2011}. Bandwidth requirements dictate that the server perform the filtering. The server application would fetch all records from the DB for this client, then filter out the misses by checking record.MeetingDate.AddMinutes(MinutesToAdjustBy).Date == StartDate.Date. Return to the client all records which pass this check, without altering the return data; i.e., MeetingDate is still in UTC. This avoids the issue of shifting calendar dates when converting to or from UTC, and allows your back end to be unaware of actual timezones.

As an aside, the perfomance in my example could be improved by querying against the DB for all client records with StartDate within 1 calendar day either way of the requested date, in order to significantly reduce the number of records fetched.

Edit: I misunderstood the question in my initial answer and got hung up on the issue of where the conversion was taking place. I'm leaving the above, since it's relevant to the issue of how to handle the conversions, but it doesn't properly answer the question.

If you're willing to trust that your clients' systems will have the correct (and more importantly, all have the same) registry entries for timezones and will support this method, you can store and pass the TimeZoneInfo.Id for both departure and arrival timezones, and use TimeZoneInfo.FindSystemTimeZoneById() during client side conversions. It puts a lot of trust in the clients but has very low impact.

If you're willing to trust an external service, you could do a similar storage of destination and arrival TZ IDs as above, and then call the service client-side (or server side, if absolutely necessary) to get the required information. See this question.

If neither of those is palatable, you're probably stuck making your own timezone table and caching it in your service, as you mentioned in the question. Again, here you can follow the same basic design: store the two TZ IDs, and have the client fetch the required info from the service in order to do the work.