How to store and compare time-zone sensitive times

Question

I have a data structure where an entity has times stored as an int (minutes into the day) for fast comparison. The entity also has a Foreign Key reference back to a TimeZone table which contains the .NET CLR ID Name and it's Standard Time/Daylight Time acronyms.

Since this information is stored as time-zone insensitive - I was wondering how in LINQ to SQL I could convert this into a UTC DateTime for comparison against other times that will be in UTC.

Just to be clear this conversion has to be done server-side so that I can execute filtering on the SQL Server and not the client. The reason for this is to ensure we take into account DST for time zones that support it.

I am using .NET 3.5 SP1 and SQL Server 2008.

Answer 1

Ideally, times should be stored in the database in UTC, and only converted to some local timezone (which would include a DST factor where appropriate) for display. This is especially true if "fast comparison" is your goal.

You might find it easiest to add an extra field which contains the UTC time, modify the clients to add this information, and run a script which one-time calculates it for existing entries.

Answer 2

I can store a CurrentOffset field that will need to be updated by a script that will be updated on the hour, every hour to determine the contextual offset.