How to store and compare time with a time zone

Question

I have a data structure where the entity has time stored as int (minutes per day) for quick comparison. The object also has a link to a foreign key back to the TimeZone table, which contains the ID.NET CLR identifier, as well as abbreviated abbreviations Time / Daylight Time.

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

To make this clear, this conversion must be done on the server side , so that I can filter on SQL Server, not on the client. The reason for this is to ensure that we consider DST for time zones that support it.

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

Answer 1

I can save the CurrentOffset field, which will need to be updated with a script that will be updated per hour, every hour, to determine the context offset.

Answer 2

Ideally, the time should be stored in a database in UTC format and only converted to some local time zone (which will include the DST factor, where necessary) for display. This is especially true if your goal is a quick comparison.

It may be easier for you to add an additional field that contains UTC time, change clients to add this information, and run a script that computes it once for existing records.