如何将SQLite中的当前时间戳存储为ticks？ [英] How can I store the current timestamp in SQLite as ticks?

问题描述

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

I have a SQLite database where I store the dates as ticks. I am not using the default ISO8601 format. Let's say I have a table defined as follows:

CREATE TABLE TestDate (LastModifiedTime DATETIME)

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

Using SQL, I wish to insert the current date and time. If I execute any of the below statements, I end up getting the date and time stored as a string and not in ticks.

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

我查看了 SQLite文档，但我不似乎找到任何选项来获取ticks中的当前时间戳。

I have looked at the SQLite documenation, but I do not seem to find any option to obtain the current timestamp in ticks.

我当然可以在C＃中定义参数，并将该值存储为System.DateTime。这确实导致datetime在tick中被存储到数据库中。

I can of course define a parameter in C# and store the value as a System.DateTime. This does result in the datetime getting stored to the database in ticks.

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

What I would like to do is be able to insert and update the current timestamp directly from within the SQL statement. How would I do this?

编辑：

存储在数据库中的数据的数据是，日期以与ADO.Net数据提供者存储的格式相同的格式存储，因此当使用ADO.Net提供程序也查询数据时，它被正确地检索为系统.DataTime .Net类型。

The reason I want the data stored as ticks in the database, is that the dates are stored in the same format as stored by the ADO.Net data provider, and so that when the data is also queried using the ADO.Net provider it is correctly retrieved as a System.DataTime .Net type.

推荐答案

进一步研究 SQLite文档以及有关日期转换的其他信息，我已经找出以下公式，这样可以产生正确的结果：

After further study of the SQLite documentation and other information found on date number conversions, I have come up with the following formula, which appears to produce correct results:

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

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

Seems like a painful way to produce something that I would expect to be available as a built-in datetime format, especially that the database supports the storing of datetime values in ticks. Hopefully, this becomes useful for others too.

更新：

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

The above formula is not perfect when it comes to daylight savings. See section Caveats And Bugs in SQLite docs regarding local time calculation.

这篇关于如何将SQLite中的当前时间戳存储为ticks？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！