如何将 SQLite 中的当前时间戳存储为刻度？

Question

我有一个 SQLite 数据库，我将日期存储为刻度。我没有使用默认的 ISO8601 格式。假设我有一个定义如下的表：

CREATE TABLE TestDate (LastModifiedTime DATETIME)

我希望使用 SQL 插入当前日期和时间。如果我执行以下任何语句，我最终都会将日期和时间存储为字符串而不是刻度。

INSERT INTO TestDate (LastModifiedTime) VALUES(CURRENT_TIMESTAMP)
INSERT INTO TestDate (LastModifiedTime) VALUES(DateTime('now'))

我查看了 SQLite 文档，但我似乎没有找到任何选项来获取当前时间戳（以刻度为单位）。

我当然可以在 C# 中定义一个参数并将该值存储为 System.DateTime。这确实会导致日期时间以刻度为单位存储到数据库中。

我想做的是能够直接从 SQL 语句中插入和更新当前时间戳。我该怎么做？

编辑：

我希望将数据作为刻度存储在数据库中的原因是，日期的存储格式与 ADO.Net 数据提供程序存储的格式相同，因此当数据也使用 ADO.Net 提供程序进行查询时，它会作为 System.DataTime .Net 类型正确检索。

Answer 1

SQLite 的这一特殊之处给我带来了很大的痛苦。

简单的方法 - 作为常规时间戳存储和检索

create table TestDate (
        LastModifiedTime datetime
);
insert into TestDate (LastModifiedTime) values (datetime('now'));
select datetime(LastModifiedTime), strftime('%s.%f', LastModifiedTime)  from TestDate;

输出： 2011-05-10 21:34:46|1305063286.46.000

痛苦的方法 - 作为 UNIX 时间戳存储和检索

您可以使用 strftime 检索刻度值。此外，要存储 UNIX 时间戳（大致相当于刻度），您可以将秒数括在单引号中。

 insert into TestDate (LastModifiedTime) values ('1305061354');

SQLite 会在内部将其存储为非 UNIX 时间戳的其他值。在检索时，您需要显式告诉 SQLite 将其作为 UNIX 时间戳进行检索。

 select datetime(LastModifiedTime, 'unixepoch') FROM TestDate;

要存储当前日期和时间，请使用 strftime('%s', 'now')。

 insert into TestDate (LastModifiedTime) VALUES (strftime('%s', 'now'));

完整示例：

create table TestDate (
        LastModifiedTime datetime
);      
insert into TestDate (LastModifiedTime) values (strftime('%s', 'now'));
select datetime(LastModifiedTime, 'unixepoch') from TestDate;

当由 sqlite3 执行时，此脚本带有打印：

2011-05-10 21:02:34 (or your current time)

Answer 2

经过进一步研究 SQLite 文档 和其他有关日期数字转换的信息，我想出了下面的公式，它似乎产生了正确的结果：

INSERT INTO TestDate(LastModifiedTime)
    VALUES(CAST((((JulianDay('now', 'localtime') - 2440587.5)*86400.0) + 62135596800) * 10000000 AS BIGINT))

似乎是一种痛苦的方式来产生我希望可以作为内置日期时间格式使用的东西，特别是数据库支持以刻度形式存储日期时间值。希望这对其他人也有用。

更新：

对于夏令时，上述公式并不完美。请参阅 SQLite 文档中有关本地时间计算的注意事项和错误部分。

Answer 3

以下将返回自 UNIX 纪元以来的毫秒数：

SELECT (strftime('%s', 'now') - strftime('%S', 'now') + strftime('%f', 'now')) * 1000 AS ticks

它的工作原理是获取自 UNIX 纪元 (%s) 以来的秒数，减去当前时间的秒数 ( %S），加上带小数位的秒数 (%f)，并将结果乘以 1000，将秒转换为毫秒。

减法和加法是为了增加值的精度而不扭曲结果。正如 SQLite 文档中所述，同一步骤中所有使用 'now' 将返回相同的值。