如何在PostgreSQL中存储DateTimeOffset

Question

我在使用Entity Framework从PostgreSQL数据库检索DateTimeOffset时遇到了问题。通过研究，我发现了这篇文章，帮助我理解了问题所在，但我无法找出如何解决它。

我有一个API，允许用户上传文件（大多数是图像），并提取图像拍摄日期并将其存储在数据库中。大多数情况下都很好用。然而，如果日期在2007年之前的3月11日至某个时间之间（日期根据年份而变化），则保存到数据库中没有问题，但在尝试检索行时，会抛出以下错误：

“本地dateTime参数的UTC偏移量与偏移量参数不匹配。”

我在postgreSQL中为CameraDate字段使用了“Timestamp With Time Zone”类型。我无法弄清楚如何使其正常工作。如果可以在不更改数据库的情况下完成，则最好不过。

示例：

2001-04-01 10:47:17-06 Works
2001-03-01 10:47:17-06 Works
2001-03-13 10:47:17-06 Doesn't work
2007-03-13 10:47:17-06 Works

这是我的代码：

Context.Files.Add(file);
Context.Entry(file).Reload();

文件类看起来像这样（为简洁起见，省略了一些字段）：

public class File
{
    [Column("FileId")]
    public override Guid ID { get; set; }
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int DisplayId { get; set; }
    public DateTimeOffset? CameraDate { get; set; }
}

- GBreen12

---

你能发一下你的代码吗？ - IndieTech Solutions

看起来 PostgreSQL 内部仅存储 UTC 时间戳。http://www.postgresql.org/docs/9.1/static/datatype-datetime.html - IndieTech Solutions

我可以发布代码，但我认为这并没有什么帮助。基本上，我只是在执行Context.Files.Add(file)然后Context.Entry(file).Reload()。错误是在Reload()上抛出的。我使用Reload是因为我需要更新一个序列字段。 - GBreen12

谢谢您的编辑，我已经撤销了我的关闭投票。请问您能告诉我们在您的PostgreSQL数据库中CameraDate字段的数据类型是什么吗？ - Matt Johnson-Pint

问题- Entity Framework 运行的计算机的时区是什么？如果Postgresql机器的时区不同，它的时区是什么？谢谢。 - Matt Johnson-Pint

Answer 1

好的，这似乎是与npgsql或npgsql.EntityFramework中的DateTimeOffset有关的错误。他们的问题跟踪器中记录了几个其他问题。特别是问题＃542非常接近 - 但具有不同的异常消息。

我建议您在那里创建一个新问题，因为他们最擅长帮助进一步诊断此问题。

- Matt Johnson-Pint

---

看起来Pgsql的开发者目前建议不要使用DateTimeOffset和timestamp with time zone。请参考从这条评论开始的讨论。 - Zoomzoom

Answer 2

要在PostgreSQL中存储DateTimeOffset，您应将其转换为协调世界时： public DateTimeOffset ToUniversalTime();

请查看问题4176 使用EF Core 6.0，可以通过为DateTimeOffset使用值转换器来实现批量配置值转换器。最近在EF Core中引入了批量配置值转换器的功能，我们正在利用它。https://docs.microsoft.com/en-us/ef/core/modeling/value-conversions?tabs=data-annotations#bulk-configuring-a-value-converter

public class DateTimeOffsetConverter : ValueConverter<DateTimeOffset, DateTimeOffset>
{
    public DateTimeOffsetConverter()
        : base(
            d => d.ToUniversalTime(),
            d => d.ToUniversalTime())
    {
    }
}

在您的DbContext中覆盖

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder
        .Properties<DateTimeOffset>()
        .HaveConversion<DateTimeOffsetConverter>();
}

- Vivien P