如何在PostreSQL中存储DateTimeOffset [英] How to store DateTimeOffset in PostreSQL

问题描述

使用实体框架从PostgreSQL数据库检索 DateTimeOffset 时遇到问题.就研究此问题而言，我发现了这篇文章可以帮助我理解问题所在，但我不知道如何解决该问题.

I am having a problem with retrieving a DateTimeOffset from a PostgreSQL database using Entity Framework. As far as researching the problem, I found this article that helps me understand what the problem is, but I can't figure out how to fix it.

我有一个API，允许用户上传文件(主要是图像)，并提取图像的拍摄日期并将其存储在数据库中.在大多数情况下，它的效果都很好.但是，如果日期在2007年之前的3月11日到4月的某个时间之间(日期因年份而异)，则会保存到数据库中，但是在尝试检索该行时会引发错误:

I have an API that allows users to upload files (mostly images) and it extracts the date that the image was taken and stores it in the database. It works great most of the time. However, if the date is between March 11 to sometime in April (date varies based on year) before 2007 it saves into the database fine but when trying to retrieve the row, it throws the error:

本地dateTime参数的UTC偏移量与offset参数不匹配.

The UTC Offset of the local dateTime parameter does not match the offset argument.

我在PostgreSQL中将带时区的时间戳类型用于 CameraDate 字段.我不知道如何使它正常工作.如果可以在不更改数据库的情况下做到这一点，那将是更好的选择.

I am using the Timestamp With Time Zone type in postgreSQL for the CameraDate field. I can't figure out how to get this to work correctly. If there is a way to do it without changing the database that would be preferable.

示例:

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();

文件类如下(为简明起见，删除了某些字段):

File class looks like this (Some fields removed for briefness):

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

推荐答案

好吧，这似乎是npgsql或npgsql.EntityFramework中 DateTimeOffset 的错误.在他们的问题跟踪器中，似乎还记录了其他一些问题.特别是问题#542 非常接近-但有不同的异常消息.

Ok, this appears to be a bug with DateTimeOffset in either npgsql or npgsql.EntityFramework. There appear to be several other issues logged in their issue tracker. In particular issue #542 is pretty close - but with a different exception message.

我建议您在那创建一个新问题，因为它们是最好的进一步诊断方法.

I suggest you create a new issue over there, as they are the best to help diagnose this further.

这篇关于如何在PostreSQL中存储DateTimeOffset的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！