Offsetting a DateTime using Entity Framework Migrations

Question

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?

viperguynaz · Accepted Answer · 2015-10-06 02:00:13Z

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.");
}

answered Oct 6, 2015 at 2:00

viperguynaz

12.2k4 gold badges33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Beaulieu Over a year ago

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.

viperguynaz Over a year ago

Actually, SQL 2008+ allows you to store datetimeoffset fields 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.

Collectives™ on Stack Overflow

Offsetting a DateTime using Entity Framework Migrations

Question

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Question

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related