locked
diffrent zone c# coding problem. RRS feed

  • Question

  • User2102072086 posted

    my web api is in india, which reads data from sqlserver ( in london) and mongo in amarica 

    now in web api c#, the date I am getting from sqlserver and mongo is manupulated in all way.

    so i need general guide line and some code like invariant or anything which is suitable. for diffrent zone problems.

    yours sincerley

    Monday, April 6, 2020 8:07 AM

All replies

  • User288213138 posted

    Hi rajemessage,

    i need general guide line and some code like invariant or anything which is suitable. for diffrent zone problems.

    Do you want to handle conversions between local time zone values and GMT?

    If so, you can try to use the T-SQL Toolbox.

    The T-SQL Toolbox provides SQL Server user-defined functions (UDFs) that convert date/time values between time zones, including GMT. T-SQL Toolbox also provides UDFs that calculate starting or ending.

    More information about the use of T-SQL Toolbox you can refer to this link:

    https://github.com/SparkhoundSQL/sql-server-toolbox

    Best regards,

    Sam

    Tuesday, April 7, 2020 5:35 AM
  • User2102072086 posted

    no no, my primery concernce is after getting data in c# i am manupulating dates so what code i should use in c# to handle mulitipule zone deplyment.

    because we do not want to shift(add or substract time ( zone)) the date passed to the c# module. 

    Tuesday, April 7, 2020 6:07 AM
  • User288213138 posted

    Hi rajemessage,

    my primery concernce is after getting data in c# i am manupulating dates so what code i should use in c# to handle mulitipule zone deplyment.

    because we do not want to shift(add or substract time ( zone)) the date passed to the c# module. 

    You can try to use the TimeZoneInfo class to convernt between any two time zones.

    Here a demo for you as a reference.

    DateTime hwTime = new DateTime(2007, 02, 01, 08, 00, 00);
    try
    {
       TimeZoneInfo hwZone = TimeZoneInfo.FindSystemTimeZoneById("Hawaiian Standard Time");
       Console.WriteLine("{0} {1} is {2} local time.",
               hwTime,
               hwZone.IsDaylightSavingTime(hwTime) ? hwZone.DaylightName : hwZone.StandardName,
               TimeZoneInfo.ConvertTime(hwTime, hwZone, TimeZoneInfo.Local));
    }
    catch (TimeZoneNotFoundException)
    {
       Console.WriteLine("The registry does not define the Hawaiian Standard Time zone.");
    }
    catch (InvalidTimeZoneException)
    {
       Console.WriteLine("Registry data on the Hawaiian Standard Time zone has been corrupted.");
    }

    Best regards,

    Sam

    Friday, May 1, 2020 8:24 AM
  • User303363814 posted

    I have a similar situation.  I have found it is much less confusing to always work with DateTimeOffset and to convert all values to my timezone as soon as they are received.  Also, everything in the database is stored as a DateTimeOffset in my timezone (if the database does not support DateTimeOffset then I store separate DateTime in my zone plus Offset that is correct for the given DateTime)

    The advantage is that I can use any tool to look at the data and I always see times that make sense to me because they correspond to walk clock time (I don't have to do any conversion from UTC or whatever, which may have a different date - I am in Australia)

    I have tried a couple of approaches over the years

    •  Using DateTime and converting to my timezone.  Not so easy and there is no indication if a DT was during daylight saving.  You don't always know what timezone a source date has come from (my server is ?somewhere? in the US)
    •  Using UTC everywhere.  A pain in the neck because everything has to be converted to local, every time - be careful not to convert twice!  You even have to convert if you don't care about the time because the date is often wrong by one.  Simple things like 'what was the date at the start of the month' become annoyingly complex.

    I have used DateTimeOffset everywhere converted to Aust time zone for the last few projects with way fewer headaches.  (Switching to MySQL was annoying because it does not support DateTimeOffset - I have two fields in the Db for each value, the local DateTime and the offset as a TimeSpan - an EntityFramework complex type makes it pretty transparent and the Db is still readable by humans).

    I created a static class to centralise all the things I need to do.  This is the general idea

    using System;
    
    namespace Whatever
    {
        public static class MyTime
        {
            static readonly TimeZoneInfo easternTimeZone = TimeZoneInfo.FindSystemTimeZoneById("AUS Eastern Standard Time");
    
            public static DateTimeOffset Now
            {
                get
                {
                    var utcNow = DateTimeOffset.UtcNow;
                    return utcNow.ToOffset(easternTimeZone.GetUtcOffset(utcNow));
                }
            }
    
            public static DateTimeOffset Today
            {
                get
                {
                    var now = Now; // Take a snapshot
                    var sod = new DateTimeOffset(new DateTime(now.Year, now.Month, now.Day));
                    return sod.ToOffset(easternTimeZone.GetUtcOffset(sod));
                }
            }
    
            public static DateTimeOffset Tomorrow
            {
                get 
                { 
                    var now = Now; // Take a snapshot
                    var sot = new DateTimeOffset(new DateTime(now.Year, now.Month, now.Day + 1));
                    return sot.ToOffset(easternTimeZone.GetUtcOffset(sot));
                }
            }
    
            public static DateTimeOffset ThisMonth
            {
                get
                {
                    var now = Now; // Take a snapshot
                    var som = new DateTimeOffset(new DateTime(now.Year, now.Month, 1));
                    return som.ToOffset(easternTimeZone.GetUtcOffset(som));
                }
            }
    
            public static DateTimeOffset ThisYear
            {
                get
                {
                    var now = Now;
                    var soy = new DateTimeOffset(new DateTime(now.Year, 1, 1));
                    return soy.ToOffset(easternTimeZone.GetUtcOffset(soy));
                }
            }
    
            public static DateTimeOffset ToMyTime(DateTime date)
            {
                if (date < new DateTime(2000, 1, 1))
                {
                    return DateTimeOffset.MinValue;
                }
    
                return new DateTimeOffset(date)
                            .ToOffset(easternTimeZone.GetUtcOffset(date));
            }
    
            public static DateTimeOffset ToMyTime(DateTimeOffset date)
            {
                return date.ToOffset(easternTimeZone.GetUtcOffset(date));
            }
        }
    }
    

    Early version of this class tried to be efficient.  That was a mistake.  It is much, much, much better to be correct and understandably correct then any tiny nanosecond 'efficiencies'

    Saturday, May 2, 2020 6:50 AM