转换DateTime.Ticks到MySQL日期时间查询 [英] Convert DateTime.Ticks to MySQL DateTime in query

问题描述

我在MySQL中的整数列存储DateTime.Ticks。

一个单独的刻度代表一个几百纳秒或千万分之一的第二。有10000蜱在一毫秒。

此属性的值表示自午夜12:00:00，1月1日以来经过的100纳秒的间隔的数量， 0001

我如何将其转换为在查询日期时间？我已经尝试了很多东西，但不能让它开始工作。

有关蜱634128921500016150我希望能得到MySQL的日期时间2010-06-23 12:06： 50

我会相信下面的应该工作，但它给'4009-06-22 12：15：50.001600'。现在看来，这2001年，每日1次和第9分钟，是关闭...如果年，天是一致的，我只能手工修复，不过分钟似乎有些奇怪。

  SELECT DATE_ADD（'0000-01-01 00:00:00'，
间隔10000000分之634128921500016150SECOND_MICROSECOND）; 
  

我试着增加更多的零，但它永远不会匹配：|

我也试过乔恩斯基特的建议，但它给几乎相同的结果。

解决方案

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

  SELECT DATE_ADD（'0001-01-01 00:00:00，
间隔10分之634121049314500000MICROSECOND）; 
  

编辑：我刚刚制定了，为什么十年都是错了。 MySQL的最小日期是在1000年因此，我建议你改变它：

  SELECT DATE_ADD（'0001-01-01 00:00:00'，
区间（634121049314500000 -  base_ticks）/ 10 MICROSECOND）; 
  

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

哎呀，你可以重订，无论你想（例如2000） - 甚至可能工作轮9分钟发行。这可能是它弥补了闰秒多年来，或者类似的东西。

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

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

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

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

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

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 :|

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

解决方案

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

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

EDIT: 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);

where base_ticks is the value of the ticks from new DateTime(1001, 1, 1).Ticks.

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日期时间查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！