0

I'm working on a backend API that's hosted on Azure and my dates are coming back in a different timezone. The way that I'm getting my dates currently is by using a custom Up() method in my Migrations folder, that looks similar to this:

public partial class MyMigration : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Users",
            c => new
                {
                    Created = c.DateTime(nullable: false, defaultValueSql: "GETDATE()"),
                })
            .PrimaryKey(t => t.ID);
 ... //

(snippet source)

I've been reading about sysdatetimeoffset() and datetimeoffset() but I wouldn't know how to put them to use in this context.

Question

Is there a way to set a database generated default DateTime with an offset using Entity Framework code-first migration?

Community
  • 1
  • 1
Dan Beaulieu
  • 19,406
  • 19
  • 101
  • 135

1 Answers1

1

Azure times (SQL, web apps, cloud services) are always in UTC time. If you want your API to return time for a specific time zone, you are better off using .NET resources to convert UTC time to your time zone of interest and letting Azure deal with/store UTC times. See Converting Times Between Time Zones on MSDN, specifically the section Converting UTC to a Designated Time Zone: 

DateTime timeUtc = DateTime.UtcNow;
try
{
   TimeZoneInfo cstZone = TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time");
   DateTime cstTime = TimeZoneInfo.ConvertTimeFromUtc(timeUtc, cstZone);
   Console.WriteLine("The date and time are {0} {1}.", 
                     cstTime, 
                     cstZone.IsDaylightSavingTime(cstTime) ?
                             cstZone.DaylightName : cstZone.StandardName);
}
catch (TimeZoneNotFoundException)
{
   Console.WriteLine("The registry does not define the Central Standard Time zone.");
}                           
catch (InvalidTimeZoneException)
{
   Console.WriteLine("Registry data on the Central Standard Time zone has been corrupted.");
}
viperguynaz
  • 12,044
  • 4
  • 30
  • 41
  • I suppose I understand why Azure would do it this way. Most people are going to be serving users across many timeszones, so the database should naturally store UTC time for uniformity. Then the application can deal with it based on the users location. Thanks for clearing this up. – Dan Beaulieu Oct 06 '15 at 13:29
  • Actually, SQL 2008+ allows you to store datetimeoffset fields https://msdn.microsoft.com/en-us/library/bb630289(v=sql.110).aspx that store the time zone offset with the datetime. However, if you are using a SQL based function like getdate() for a default value, the value will always be stored as server time - UTC in Azure. – viperguynaz Oct 06 '15 at 13:49