将 Sqlite BigInt 转换为日期
我有一个 Sqlite 数据库，用作 Quartz.net 调度程序作业的 ado.net 作业存储。在一个表中，名为 START_TIME 的列的类型为 big int。
有没有办法将 bigint 转换或转换为日期值？我希望能够查询数据库以查看哪些作业被安排在什么日期/时间，而诸如 633869892000000000 这样的值是没有意义的。
谢谢！
I have a Sqlite database that I am using as an ado.net job store for my Quartz.net scheduler jobs. In one table, a column called START_TIME is of type big int.
Is there a way to cast or convert a bigint to a date value? I would like to be able to query the database to see which jobs are scheduled at what date/time and a value such as 633869892000000000 is meaningless.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这似乎对我有用；
致谢； https://forums.xamarin.com/discussion/ 54282/sqlite-datetime-convert-to-net-datetime
This seemed to work for me;
Credits to ; https://forums.xamarin.com/discussion/54282/sqlite-datetime-convert-to-net-datetime
假设
START_TIME是标准的 UNIX 纪元以来的秒数，您可以使用：参见 http://www.sqlite.org/lang_datefunc.html，“修饰符”部分。
Assuming that
START_TIMEis the standard seconds-since-Unix-epoch, you can use:See http://www.sqlite.org/lang_datefunc.html, "Modifiers" section.
该日期时间存储为刻度。以下链接涵盖了有关蜱虫的更多详细信息。
DateTime.Ticks 属性
要将 Ticks 转换为日期时间，您可以使用 SQL 查询，因为
Ticks 可以在 C Sharp 中转换为 DateTime，如下所示。
This date time is stored as Ticks. Following link covers bit more details about ticks.
DateTime.Ticks Property
To convert Ticks into date time, you can use SQL query as
Ticks can be converted into DateTime as below in c sharp.
距普通时代 (CA) 或圣诞节开始还有数百纳秒（1 ns = 1/10^9 秒）。
要将日期从此格式转换为正常日期：
1. 首先，您需要通过以下公式将 633869892000000000=YourDateHundredsOfNanoSecondsCA of 100'nanoseconds 转换为 Unixepoch 格式（自 1970 年 1 月 1 日到您的日期的秒数）：
(YourDateHundredsOfNanoSecondsCA-(ROUND(1969*( 365+1/4-1/100+1/400),0)-2)*24*60*60*1E9/100)*100/1E9，
其中：
1.1回合(1969*(365+1/4- 1/100+1/400),0)-2)=DaysOf1970Years - 从耶稣诞生到 1970 年 1 月 1 日的天数。
1.2 HundredsOfNanoSecondsOf1970Years=DaysOf1970Years*24*60*60*1E9/100 - 从从普通时代开始到 Unix 纪元开始（1970 年 1 月 1 日）。
1.3 YourDateInUnixEpochSeconds=(YourDateHundredsOfNanoSecondsCA-HundredsOfNanoSecondsOf1970Years)*100/1E9 - 从 01/01/1970 到您的日期的秒数。
2.通过函数将Unix Epoch格式日期转换为可读格式（我以SQLite为例）：
SELECT datetime(YourDateInUnixEpochSeconds, 'unixepoch');
对于您的示例，您可以将此字符串复制到 SQLiteStudio 中的 SQL 查询编辑器并运行：
SELECT datetime((633869892000000000-(ROUND(1969*(365+1/4-1/100+1/400),0)-2)* 24*60*60*1E9/100)*100/1E9, 'unixepoch');
结果你会得到 '2010-12-19 17:00:00'
It's hundreds of nanoseconds (1 ns = 1/10^9 second) from the begining of Common Age (CA) or Christmas.
To convert date from this format to normal date:
1. First you need to convert 633869892000000000=YourDateHundredsOfNanoSecondsCA of 100'nanoseconds to Unixepoch format (seconds since 1st of January 1970 till Your Date) by formula:
(YourDateHundredsOfNanoSecondsCA-(ROUND(1969*(365+1/4-1/100+1/400),0)-2)*24*60*60*1E9/100)*100/1E9,
where:
1.1 ROUND(1969*(365+1/4-1/100+1/400),0)-2)=DaysOf1970Years - round count of days from Jeasus born till January, 1 1970.
1.2 HundredsOfNanoSecondsOf1970Years=DaysOf1970Years*24*60*60*1E9/100 - hundreds of nanoseconds from the begining of Common Age till the begining of Unix Epoch (1st of January 1970).
1.3 YourDateInUnixEpochSeconds=(YourDateHundredsOfNanoSecondsCA-HundredsOfNanoSecondsOf1970Years)*100/1E9 - count of seconds from 01/01/1970 till Your Date.
2. Convert Unix Epoch format date to readable format by function (I give example for SQLite):
SELECT datetime(YourDateInUnixEpochSeconds, 'unixepoch');
For your example you can copy this string to SQL query editor in SQLiteStudio and run:
SELECT datetime((633869892000000000-(ROUND(1969*(365+1/4-1/100+1/400),0)-2)*24*60*60*1E9/100)*100/1E9, 'unixepoch');
and as a result you will get '2010-12-19 17:00:00'
这个解决方案只给我一列空值：
select datetime(timestmp, 'unixepoch') fromlogging_event
This solution only give me a column of empty values:
select datetime(timestmp, 'unixepoch') from logging_event