在Azure SQL上保存Datetimeoffset无法正常工作 [英] Saving Datetimeoffset on Azure SQL not working

问题描述

我在Azure SQL服务器上遇到问题，在我的本地计算机上，我可以将datetimeoffset保存到db中，并且正确保存了时区偏移，例如下方

I'm having a problem with Azure SQL server, on my local machine I can save a datetimeoffset into the db and the timezone offset is correctly saved like below

2017-03-31 00:00:00.0000000 -05:00

但是天蓝色丢失了时区偏移，我的列是datetimeoffset类型，并且我使用此 DateTimeOffset.UtcNow.UtcDateTime 或 DateTimeOffset.Now 获取datetimeoffset但似乎没有办法，总是像下面这样保存

however on azure is losing the timezone offset, my columns are of datetimeoffset type and I'm getting my datetimeoffset using this DateTimeOffset.UtcNow.UtcDateTime or DateTimeOffset.Now but neither way seems to work, is always being saved like the below

2017-03-31 00:00:00.0000000 +00:00

如何在Azure SQL服务器上保存正确的DateTimeOffset.

how can I save the correct DateTimeOffset on Azure SQL server.

我正在使用实体框架代码优先迁移"，我只是创建实体并使用上面的任意一行然后使用context.SaveChanges()分配DateTimeOffset.

I'm using Entity Framework Code First Migration, I just create the entity and assign the DateTimeOffset using any of the line above and then context.SaveChanges().

var entity = Mapper.Map<CarSearchForm, CarSearches>(model);
 ctx.CarSearches.Add(entity);
 ctx.SaveChanges();

然后自动映射器配置文件是这样的

and then automapper profile is like this

CreateMap<CarSearchForm, CarSearches>()
            .ForMember(dest => dest.RequestedDate, opts => opts.MapFrom(src => DateTimeOffset.UtcNow.UtcDateTime))
            .ForMember(dest => dest.PickupTime, opts => opts.MapFrom(src => src.TimePickup))
            .ForMember(dest => dest.DropoffTime, opts => opts.MapFrom(src => src.TimePickup));    

这是模型

namespace Data.Entities
{
    public class CarSearches
    {
        public int CarSearchesId { get; set; }
        [Required]
        public string PickupPlace { get; set; }
        [Required]
        public DateTimeOffset PickupDate { get; set; }
        [Required]
        public DateTimeOffset PickupTime { get; set; }
        [Required]
        public DateTimeOffset DropoffDate { get; set; }
        [Required]
        public DateTimeOffset DropoffTime { get; set; }       
        [Required]
        public CarTransmission Transmission { get; set; }
        [Required]
        public DateTimeOffset RequestedDate { get; set; }        
    }

}

我只对上面的RequestedDate属性感兴趣，我无法弄清楚为什么不在Azure上保存时区偏移，而是在本地工作.

I'm only interested in the RequestedDate property above, I can't figure it out why is not saving the timezone offset on Azure but works locally.

谢谢

推荐答案

...我正在使用此 DateTimeOffset.UtcNow.UtcDateTime 或 DateTimeOffset.Now

• 第一个总是为您提供UTC DateTime 值(偏移量 +00:00 ).它等效于 DateTime.UtcNow .

  • The first one is always going to give you a UTC DateTime value (offset +00:00). It is equivalent to DateTime.UtcNow.

    第二个将为您提供一个 DateTimeOffset 值，该值的本地时间和偏移量与运行它的计算机相匹配.

    The second one will give you a DateTimeOffset value, whose local time and offset match the computer where it is running.

    • 在本地计算机上，您会看到 -05:00 ，因为它使用了计算机的本地时区设置.

    • On your local computer, you see -05:00 because it uses your computer's local time zone setting.

    在Azure上，您看到 +00:00 .这是因为大多数Azure服务器和服务均设置了其时区以在UTC中运行.

    On Azure, you see +00:00. This is because most Azure servers and services are set with their time zone to run in UTC.

    通常，这是最佳实践服务器，尤其是云中的服务器.服务器通常需要与世界各地的客户端连接，并相互交换数据.UTC是唯一明智的时区.

    In general, this is the best practice for servers, especially those in the cloud. Servers often need to connect with clients all over the world, and interchange data with each other. UTC is the only time zone that is sensible.

    另一种考虑方法是，如果机器的时区设置为UTC，则 DateTime.Now 和 DateTime.UtcNow 将给出相同的日期和时间值，但一个将其 .Kind 属性设置为 DateTimeKind.Local ，另一个将其设置为 DateTimeKind.Utc .由于 DateTimeOffset (没有)没有( Kind )，因此无法区分 DateTimeOffset.Now 和 DateTimeOffset.UtcNow 在时区设置为UTC的计算机上.

    Another way to think about it is that if a machine's time zone is set to UTC, DateTime.Now and DateTime.UtcNow will give the same date and time values, but one will have its .Kind property set to DateTimeKind.Local and the other will have it set to DateTimeKind.Utc. Since DateTimeOffset doesn't have a Kind (thankfully), one cannot distinguish between DateTimeOffset.Now and DateTimeOffset.UtcNow on a machine whose time zone is set to UTC.

    关于该怎么做-如果您希望考虑其他时区，则首先必须知道是哪个时区.然后，您可以使用 TimeZoneInfo 类(如果使用的是Windows时区)，也可以使用 Noda Time库(如果您使用的是IANA时区)将当前UTC时间转换为该特定时区中的 DateTimeOffset .

    As far as what to do about it - if you desire some other time zone to be taken into account, you'll first have to know which time zone that is. Then you can use either the TimeZoneInfo class (if you're using Windows time zones), or the Noda Time library (if you're using IANA time zones) to convert the current UTC time to a DateTimeOffset in that particular time zone.

    使用 TimeZoneInfo :

    DateTime utcNow = DateTime.UtcNow;
    TimeZoneInfo tz = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
    DateTimeOffset easternNow = TimeZoneInfo.ConvertTimeFromUtc(utcNow, tz);
    

    使用诺达时间:

    Instant now = SystemClock.Instance.Now;
    DateTimeZone tz = DateTimeZoneProviders.Tzdb["America/New_York"];
    DateTimeOffset easternNow = now.InZone(tz).ToDateTimeOffset();
    

    这篇关于在Azure SQL上保存Datetimeoffset无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！