在SQLite.Net中存储DateTimeOffset

Question

在一个 WinRT (Windows 8.1 Store App) 项目中，我正在使用 SQLite.Net-PCL 和 SQLiteNetExtensions NuGet 包将数据存储在本地 SQLite 数据库文件中。我的一些数据模型（也称为表）包含 DateTimeOffset 类型的属性。目的是在不丢失偏移信息的情况下存储这些属性。（原因是用户可以在指定日期/时间时输入时区信息，这些信息必须存储在数据库中。）

我知道在创建 SQLiteConnection 时可以设置 storeDateTimeAsTicks 参数，并将其设置为 false，强制所有 DateTime 属性以 ISO 格式的文本形式存储 - 然而，这对 DateTimeOffset 属性没有任何影响，因为它们总是自动转换为 UTC 并作为表示 ticks 的数字存储。

我可以想到以下四种方法：

- 手动将 DateTimeOffset 转换为 string 属性并存储这些属性。 - 提取 DateTimeOffset 的 DateTime 和 offset（作为 TimeSpan 日期类型），并将它们分别存储在两个单独的列中。 - 在另一个表的两个列中存储 DateTimeOffset 的 DateTime 和 TimeSpan（offset）部分，并维护对该表的引用。

但是对于这两种方法，我需要向数据模型添加其他属性，使用 [Ignore] 属性标记原始 DateTimeOffset 属性，并处理手动转换（双向）。由于我需要将其应用于许多不同的数据模型类，所以维护似乎太难了。

在这种情况下，我需要定义一个自定义数据类型（以指定如何存储DateTime和TimeSpan部分），并且不能使用默认的.NET DateTimeOffset类型。

• 可以使用SQLiteNetExtensions的TextBlob属性将DateTimeOffset序列化为单个文本列

但是这种方法感觉有点巧妙，我需要确保只使用SQLiteNetExtensions的扩展方法进行数据库插入/更新，并且仍然需要在所有数据模型类上添加一个额外的string属性......

所以，我的问题是：是否有更直接、明显的解决方案我忽略了？

- andreask

---

我还没有成功测试过，但你尝试使用过SQLite.Net.DateTimeOffset nuGet包吗？ 使用文档在这里：https://github.com/mobilemotion/SQLite.Net.DateTimeOffset - nullPainter

谢谢 @nullPainter - 因为没有人能回答我的问题，我自己编写并发布了那个 Nuget 库。我应该在有更多时间的时候尽快发布一个回答。 - andreask

旁注：DateTimeOffset 几乎从不与 DateTime + "用户时区" 相同。实际上，大多数人关心的部分是时区，或者经常围绕 DST 的行为（偏移量是次要考虑因素）。如果您存储绝对、始终映射到相同瞬间的时间，则实际区域有些不重要。另一方面，如果任何未来的日期/时间可能会合理地更改偏移量（通常是调整 DST），例如日历/计划程序，则仅存储偏移量不太可能完全有所帮助。 - Clockwork-Muse

Answer 1

由于没有人提出潜在的解决方案，但该问题仍受到关注，我决定报告我是如何解决这个问题的：

方法1： 原始问题中提出的场景包括一个移动应用程序，其中包含API数据模型类（用于序列化/反序列化JSON以及与后端REST服务上传/下载），DB数据模型类（表示SQLite表）和各种ViewModel类，用于MVVM样式的演示层。API模型和DB模型几乎相同（除了JSON序列化和SQLite OR映射所必需的属性外），唯一的结构差异在于表示日期/时间的属性在API类中为string类型，在DB类中为DateTimeOffset类型。从后端下载数据并上传数据之前，使用Automapper将API模型和DB模型转换为彼此。我只是从Automapper配置中删除了字符串到DateTimeOffset的转换，并修改了DB数据模型类，使DateTimeOffset值表示为字符串，这意味着它们存储为格式化文本在SQLite中（幸运的是，在DB层上不需要进行任何日期/时间计算）。从后端接收到的JSON对象包含时区信息，因此我可以直接将这些值传递给DB模型，从而确保DB表始终包含完全格式化的日期/时间字符串，包括时区偏移量。现在，将字符串转换为DateTimeOffset发生在将DB数据模型创建为ViewModel类时。显然，这比以前发生得更频繁（在将API模型转换为DB模型时），导致了一些额外的开销，但我可以接受这个开销，因为我不再需要担心SQLite数据类型问题。

方法2： 由于方法1可能无法适用于所有情况，我想出了一种基于原始问题中提出的4个潜在解决方案中的第一个解决方案的替代解决方案，但减少了手动工作量。我创建了一个自定义属性[DateTimeOffsetSerialize]，可分配给SQLite数据模型类中的DateTimeOffset属性，并创建了一个后期构建任务，在构建完成后反编译程序集并扫描程序集中的所有类，以查找那些标记的属性。对于每个这样的标记属性，会自动创建一个类型为字符串的副本属性，其中包含原始属性的序列化值，并且该新创建的字符串属性将用作SQLite表列（原始的DateTimeOffset属性自动用[Ignore]属性标记）。

这个解决方案可以通过NuGet包获得，并且已经在GitHub上开源（该GitHub页面还包含详细的使用说明）。

- andreask