如何在SQLite中将当前时间戳存储为ticks？

Question

如何在SQLite中将当前时间戳存储为ticks？

11

我有一个SQLite数据库，其中将日期存储为ticks（时间戳）。我没有使用默认的ISO8601格式。假设我有一个定义如下的表：

CREATE TABLE TestDate (LastModifiedTime DATETIME)

我想使用SQL插入当前的日期和时间。如果我执行下面任意一条语句，最终得到的将是以字符串形式存储的日期和时间，而不是以ticks形式。

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

我已经查看了SQLite文档，但似乎没有任何选项可以获取当前时间戳的ticks值。

当然，我可以在C#中定义一个参数并将该值存储为System.DateTime。这将导致日期时间以ticks形式存储到数据库中。

我想要做的是能够直接从SQL语句中插入和更新当前时间戳。我该如何做？ 编辑： 我希望数据以ticks格式存储在数据库中，因为日期以与ADO.Net数据提供程序存储的格式相同存储，这样当使用ADO.Net提供程序查询数据时，它将正确地检索为System.DataTime .Net类型。

- Elan

要将当前时间戳插入数据库，只需添加---> DateTime('now')。 - user956584

3个回答

4

在进一步研究SQLite文档和日期数字转换的其他信息后，我得出了以下公式，该公式似乎可以产生正确的结果：

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

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

更新：

上述公式在夏令时方面并不完美。请参阅 SQLite 文档中关于本地时间计算的 Caveats And Bugs 部分。

- Elan

1

您的公式不正确，因为它没有考虑到夏令时的变化。NTFS 中的 LastModifiedTime 元数据以 UTC 格式存储，因此您的转换存在错误。 - JasDev

1

没错。我添加了一条关于本地时间计算的注释。您对改进公式有什么建议吗？ - Elan

1

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

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

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

减法和加法是为了增加值的精度，而不会使结果偏离。正如SQLite文档中所述，在同一步骤中使用“now”的所有用途将返回相同的值。

- Rob

请注意，此代码返回的时间是以毫秒为单位计算的。如果时间是您的主键，则可能不唯一。 - kofifus

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- MrAnonymous · Accepted Answer

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)