I realize this is a bit subjective, but I'm hoping to pick everyones brain here on how they deal with multiple timezones? There are a variety of similar questions here and an equally wide variety of accepted answers.
How have you delt with this in apps you've built, and what issues did you have to overcome?
You always store date/time in one time zone(9 out of 10 it is London time) and convert it on display to the timezone of your user. This is strictly application level issue not the db.
I think the new best practice with sql server 2008 is to always use the datetimeoffset datatype. normalizing dates to UTC is also good practices; but not always feasible or desirable.
See this blog post for more thoughts: http://blogs.msdn.com/b/bartd/archive/2009/03/31/the-death-of-datetime.aspx
+1 for @kubal5003.
Display of dates and times is always complicated by culture and time zone, so its always best to use the layer closest to the user (e.g. the browser or local application) to do this. It also moves some of the load from the database to the user's machine.
There is an exception for server-generated reports though. So I store the time zone name/ID (occasionally just the offset/bias) to find the start of day. This may be system-wide or on a per client/brand basis.
For web applications I usually detect a user's default time zone via geolocation (this is rarely wrong since geo data is quite accurate now).
At work we manage several clocks at once, not only time zones but also some more esoteric clocks used for spacecraft navigation.
The only things that really matter are: that you are consistent with ONE AND ONLY ONE CLOCK, whichever you pick, and that you have the APPROPRIATE CLOCK CONVERSIONS for when you need a VIEW OF TIME in a CLOCK DIFFERENT FROM YOUR ONE AND ONLY ONE CLOCK.
So:
One and Only One Clock: Pick the simplest one which will solve your problem, most likely this will be UTC (what some people would -incorrectly- call Greenwich, but the point remains: the zero line).
Appropriate clock conversions: This depends on your application, but you need to ask and answer the following 2 questions: How much resolution do I need? Do I need to make sure I account for leap seconds? Once you answered, you may be able to pick standard libraries or more estoreric ones. Again, you must ask these questions.
View of time: when someone picks a view of time (say, Pacific Time) simply call the appropriate clock conversion on demand.
Really, that's it.
As for libraries, I use Python for scripting, but NAIF Spice Library for mission design, and in-house code for spacecraft navigation. The difference between them is simply the resolution and the reliability on having accounted for everything you need to account for (Earth rotation, Relativity, time dilation, leap seconds, etc.) Of course, you will pick the library that fits your needs.
Good luck.
Edit:
I forgot to mention: don't try to implement your own time management library - use an off the shelve one. If you try, you may be successful, but your real project will die and you will only have one average date time library to show for it. Maybe I'm being exaggerated, but making a solid, general purpose date time library is far from trivial, i.e., it is a project in itself.
One of the projects i'm working on i'm using SQL Server 2005 and GETUTCDATE() to store the date in UTC format.
You need to maintain the user timezone information and all time zone information. And always store the time in UTC.And when you want to display that information to particular user then just access this user's timezone(which is stored for this user) and add or subtract that amount of time from the time stored in UTC and display this.That will be in the timezone of the user who is viewing the information.