在SQLite数据库（Delphi）中存储日期时间值的最佳方法

Question

我将在SQLite数据库中存储日期时间值（使用Delphi和DISqlite库）。数据库的性质是永远不需要在计算机或系统之间传输，因此互操作性不是限制。我的重点是阅读速度。日期时间字段将被索引，我将经常进行搜索，并按顺序读取数千个日期时间值。

由于SQLite没有显式的日期时间数据类型，有几种选择：

- 使用REAL数据类型并直接存储Delphi的TDateTime值：最快，在加载时无需从字符串转换；无法使用SQLiteSpy等数据库管理器调试日期，因为日期不可读。不能使用SQLite日期函数 (?) - 使用简单的字符串格式，例如YYYYMMDDHHNNSS：需要转换，但在CPU上相对容易（无需扫描分隔符），数据可读性强。仍然无法使用SQLite日期函数。 - 做其他事情。建议做什么？

我已经阅读了http://www.sqlite.org/lang_datefunc.html，但没有提到使用什么数据类型，而且由于我没有受过正式的编程教育，我不太明白对儒略日的关注。为什么需要额外的转换？我将经常读取这些值，因此在字符串和TDateTime之间进行任何额外的转换都会增加显著的成本。

- Marek Jedliński

---

我也看过https://dev59.com/X3I-5IYBdhLWcg3wR2Nr，但它并没有回答我的问题，即什么是最佳方式，或者是否有一个真正强制使用ISO字符串格式（和相关转换）而不是直接插入Delphi的TDateTime值的理由。 - Marek Jedliński

Answer 1

您可以使用SQLite支持的一种字符串格式，例如YYYY-MM-DD HH:MM:SS.SSS。

这将与YYYYMMDDHHNNSS一样容易 - 您仍不需要扫描分隔符，因为所有数字都是固定长度的 - 并且您将获得SQLite日期函数支持。

如果您需要SQLite日期函数支持，我建议使用该方法。

如果不需要，则建议使用REAL值。您仍然可以将它们彼此比较（较大的数字在时间上更晚），并将日期和时间分别考虑（小数点之前和之后），而无需转换为TDateTime。

- Blorgbeard

---

我已经广泛使用了这个 ASCII 解决方案，包括 CSV 和其他数据库，并且这就是我会做的。此外，SQLite 日期函数也可以使用。三赢。 - Warren P

Answer 2

一种折衷的方法是使用Delphi的DateTimeToJulianDate将REAL值存储为儒略日。这样做可以保持读取速度快，转换过程中几乎不会损失性能，并且仍然以在Delphi之外有意义的格式存在。

- Nick Bradbury

---

我之前使用了REAL值，其中一个缺点是数据库中的值不是人类可读的，这在使用SQL工具进行调试/分析时很不方便。 - Edwin Yip

Answer 3

对于这个问题，我通常使用整数数据类型并存储类似Unix时间戳值的数据（例如#自2000年1月1日以来的秒数）。从TDateTime计算这个t值等同于乘以/除以86400并添加一个“since”常量。

如果您需要更高的精度，可以将DateTime作为FILETIME（例如int64）使用，它具有100纳秒的增量。SysUtils中有转换例程，您的时间戳存储在UTC中。

- Ritsaert Hornstra

Answer 4

我不确定这个答案是否适用于DISqlite库，但是...
以下是一些代码，展示了我在Delphi 2010和Tim Anderson的SQLite3包装器中使用的有效方法。

创建字段的SQL：

  sSQL :=  'CREATE TABLE [someTable] (' +
            '  [somefield1] VARCHAR(12),' +
            '  [somefield2] VARCHAR(12),' +
            '  [myDateTime] DATETIME );';

用SQL填充字段：

 sSQL := 'INSERT INTO someTable(somefield1, somefield2, myDateTime)' + 
         '  VALUES ( "baloney1", "baloney2","' + FloatToStr(Now) + '");';

从字段中检索数据的示例：

var
sDBFilePathString: string;
sl3tbl: TSqliteTable;
fsldb : TSQLiteDatabase;
FromdbDTField : TDateTime;

begin
   ...
   ... 
    fsldb := TSQLiteDatabase.Create(sDBFilePathString); 
    sl3tbl := fsldb.GetTable('SELECT * FROM someTable');
    FromdbDateTime := StrToFloat(sl3tbl.FieldAsString(sl3tbl.FieldIndex['myDateTime']));
    Showmessage('DT: ' + DateTimeToStr(FromdbDTField));
end;

结果：

**DT: 10/10/2013 1:09:53 AM**

就像我在第一行提到的那样 - 我不知道这是否适用于DISqlite库，但如果可以的话，这是一个相当干净的处理方式。 我将把它留给您来使事情更漂亮或更优雅。

- TheSteven

Answer 5

如果您只关心数据库层面的人类可读格式，您可以存储两个单独的字段，例如：

DELPHI_DATE REAL（DOUBLE，如果可能的话，但我不知道SQLite），已索引。所有编程查询和比较都应使用此字段。

HUMAN_READABLE_DATE Varchar（23）格式为'YYYY-MM-DD HH：MM：SS.SSS'。如果真的有必要，可以进行索引。大多数人类输入查询应包括此字段，并且您可以使用（如其他人所说）SQLite日期函数。

它有一些缺点：

• 数据库和网络流量的空间消耗增加，
• 插入操作将需要更长时间，因为需要转换，
• 如果在程序外部更新，则没有自动同步值

如果适合您的特定需求，则由您决定。

- jachguate