将DateTime.Ticks转换为MySQL DateTime在查询中 [英] Convert DateTime.Ticks to MySQL DateTime in query

问题描述

我的MySql中存有一个整数列存储DateTime.Ticks。

I have a integer column in MySql storing the DateTime.Ticks.

单个刻度代表一百纳秒或一千万分之一一秒钟。在一毫秒内有10,000个刻度。

A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond.

此属性的值表示从1月1日午夜12:00:00起经过的100纳秒间隔， 0001

The value of this property represents the number of 100-nanosecond intervals that have elapsed since 12:00:00 midnight, January 1, 0001

如何将其转换为查询中的DateTime？我试过很多东西，但是无法让它工作。

How can I convert this to a DateTime in a query? I've tried many things, but cannot get it to work.

对于ticks 634128921500016150我希望得到mysql datetime'2010-06-23 12:06： 50'

For the ticks 634128921500016150 I hope to get the mysql datetime '2010-06-23 12:06:50'

我相信以下内容应该可以工作，但它会给出4009-06-22 12：15：50.001600。似乎是在2001年，1天和9分钟之间...如果年和日是一致的，我可以手动修复，但分钟似乎有点奇怪。

I would have believed the following should work, but it gives '4009-06-22 12:15:50.001600'. It seems it's off by 2001 years, 1 day and 9 minutes... If the years and days is consistent, I can just fix it manually, but the minutes seems a little odd.

SELECT DATE_ADD('0000-01-01 00:00:00',
  INTERVAL 634128921500016150/10000000 SECOND_MICROSECOND);

我尝试添加更多的零，但从不匹配：|

I've tried adding more zeros, but it never matches :|

我也尝试过Jon Skeet的建议，但它给出了几乎相同的结果（几分之一的不同）

I also tried Jon Skeet's suggestion, but it gives nearly the same result (some fraction of a second different)

推荐答案

而不是使用SECOND_MICROSECOND添加，请尝试通过MICROSECOND添加：

Rather than adding using SECOND_MICROSECOND, try just adding via MICROSECOND:

SELECT DATE_ADD('0001-01-01 00:00:00',
  INTERVAL 634121049314500000/10 MICROSECOND);

编辑：我刚刚解决了为什么这些年是错误的。 MySQL的最短日期是1000年。所以我建议你将其更改为：

I've just worked out why the years are so wrong. MySQL's minimum date is the year 1000. So I suggest you change it to:

SELECT DATE_ADD('0001-01-01 00:00:00',
  INTERVAL (634121049314500000 - base_ticks)/10 MICROSECOND);

其中 base_ticks 是蜱的值从新的DateTime（1001,1,1）.Ticks 。

如果你想要例如2000） - 甚至可能在9分钟的问题上工作。这可能是多年来的闰秒，或类似的东西。

Heck, you could rebase wherever you want (e.g. 2000) - that might even work round the 9 minutes issue. It's possible that it's making up for leap seconds over the years, or something like that.

这篇关于将DateTime.Ticks转换为MySQL DateTime在查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！