0

I'm in charge of coming up with a solution for implementing user-specific time zones in a brownfield application. Every single date that is stored in the database is stored in the server's local time. There is no time zone or offset information stored with these dates.

This is a very large application, so I realize this will be a lot of work. My concern is that we do this with as little risk as possible. Has anyone done this before? If so, what's the best approach, and what are some pitfalls to avoid?

The current thinking is that we convert all the data in our date columns which have a meaningful time component to UTC. Then, in the presentation layer, we'll need to run a function that converts a datetime object from UTC to the user's time zone at the time when we need to display it to the user.

It's an ASP .NET app with a SQL Server 2008 database. I mention this because there may be some useful tools within those technologies which I don't know about that we can use to help things go smoother.

Brandon Montgomery
  • 6,924
  • 3
  • 48
  • 71

1 Answers1

1

This kind of requirement in a new code is almost a perfect fit for T-SQL's new datetimeoffset data type. But in legacy code you are going to have a few problems.

There is a good article on the new datetimeoffset type called The Death of DateTime? which explores the issues with datetime quite well.

In short here are the issues you need to address:

  • Which date only fields (i.e. time must always be 00:00) can you exclude.
  • What timezones have datetimes been recorded in
  • Which timezones have a separate daylight-saving or summer time offset
  • What are the exact date ranges of the daylight saving for all years you have recorded dates in
  • How can you work out the offset for each datetime value in your data
  • How will you interpret overlapping hours at offset changes

Once you have the data split up, and have worked out the offset for each group you just call:

TODATETIMEOFFSET (expression , time_zone)

When you read a datetimeoffset you get the local time as before, plus the offset from UTC when it was recorded.

I say is almost perfect, if you need to convert a remotely recorded datetime from last year sometime, to a local time, you need to know what the local offset was when the time was recorded.

Stephen Turner
  • 7,125
  • 4
  • 51
  • 68