Handling timezone differences

Question

I am creating a small CRM and I need to be able to take into account the fact that the software will be used internationally and therefore dates and offsets will be an issue.

Specifically, the software needs to be able to show leads that need following up for a specified date depending on the persons timezone. e.g When logging into the software all leads that need following up need to be displayed for the current day for the persons timezone.

I have the following database schema where I am storing the dates as datatype datetimeoffset

When registering I am also capturing the persons timezone and saving that into another database table (e.g. "Cen. Australia Standard Time") based on the C# TimeZones.

For this example the FollowUpDate is simply being stored in the database as a C# DateTime.Now;

I can use the following code to work out date offsets based on the FollowUpDate that is stored in the database as well as leveraging the persons timezone.

var FollowUpDate = DateTime.Now;
var result = new DateTimeOffset(FollowUpDate,             TimeZoneInfo.FindSystemTimeZoneById("Cen. Australia Standard Time")
            .GetUtcOffset(FollowUpDate));

My question is using the above how would I get leads for the current day for the person based on the FollowUpDate? I'm assuming I can use some sort of TimeSpan calculation??

Also, is the above the best way to handle this scenarion? I have done some research but it's all a bit cryptic tbh

Thanks!

Answer 1

Here is a static class that you may find useful. If you are at a point where you can tweak the design then you may want to think about if you really need to save DateTimeOffsets in your database the way you are (it is basically a DateTime with an offset designator). It can be done, however, to me, it seems unnecessary. Feel free to use of modify if you would like. When I use I wrap it with another class that is created with a reference to a specific time zone instance and then all calculations can be done from there.

var usersTimeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById("Cen. Australia Standard Time");
var utcTime=  myDb.GetSomeUTCDateTime();
var userslocalTime = TimeZoneConversion.FromUTCToSpecificTimeZone(usersTimeZoneInfo, utcTime); 

usersLocalTime.AddsHours(2);
//Display some time and save

var utcTimeToSave = TimeZoneConversion.FromSpecificTimeZoneToUTC(usersTimeZoneInfo,userLocalTime); 
myDb.SaveUTCTime(utcTimeToSave);

The pseudo code above uses the static class attached below.

namespace MyApp.Core.DateTime
{
    public static class TimeZoneConversion
    {
        public static System.DateTime FromSpecificTimeZoneToUTC(string specificZone, System.DateTime specificTimeZoneDateTime)
        {
            TimeZoneInfo fromZone = TimeZoneInfo.FindSystemTimeZoneById(specificZone);
            return TimeZoneInfo.ConvertTimeToUtc(specificTimeZoneDateTime, fromZone);
        }

        public static System.DateTime FromSpecificTimeZoneToUTC(TimeZoneInfo fromZone, System.DateTime specificTimeZoneDateTime)
        {
            System.DateTime temp = System.DateTime.SpecifyKind(specificTimeZoneDateTime, DateTimeKind.Unspecified);
            return TimeZoneInfo.ConvertTimeToUtc(temp, fromZone);
        }

        public static System.DateTime FromUTCToSpecificTimeZone(TimeZoneInfo toZone, System.DateTime UTCTimeZoneDateTime)
        {          
            return TimeZoneInfo.ConvertTimeFromUtc(UTCTimeZoneDateTime, toZone);
        }

        public static TimeSpan GetTimeZoneOffsetDifference(TimeZoneInfo oldZone, TimeZoneInfo newZone)
        {           
            var now = DateTimeOffset.UtcNow;
            TimeSpan oldOffset = oldZone.GetUtcOffset(now);
            TimeSpan newOffset = newZone.GetUtcOffset(now);
            TimeSpan difference = oldOffset - newOffset;
            return difference;
        }

        public static System.DateTime FromUTCToSpecificTimeZone(string totimezone, System.DateTime UTCTimeZoneDateTime)
        {
            TimeZoneInfo toZone = TimeZoneInfo.FindSystemTimeZoneById(totimezone);
            return TimeZoneInfo.ConvertTimeFromUtc(UTCTimeZoneDateTime, toZone);
        }

        public static System.DateTime FromLocalTimeZoneToUTC(System.DateTime localDateTime)
        {
            return localDateTime.ToUniversalTime();
        }

        public static string GetServerTimeZoneID()
        {
            return "Eastern Standard Time";
        }


        public static int GetServerDatabaseTimeZoneID()
        {
            return 40;
        }

        public static string TimeZoneShortName(TimeZoneInfo tzi)
        {
            switch (tzi.StandardName)
            {
                case "Eastern Standard Time": return "EST";
                case "Hawaiian Time": return "HST";
                case "Dateline Time": return "GMT";
                case "Alaskan Time": return "";
                case "Pacific Standard Time": return "PST";
                case "Mountain Standard Time": return "MST";
                case "Central Standard Time": return "CST";
                case "Canada Central Standard Time": return "CST";
                case "GMT Standard Time": return "GMT";
                default:
                    return "";

            }
        }
    }
}