.NET DateTime，与 OADate 相互转换时的分辨率不同吗？

发布于 2024-09-19 19:16:36 字数 445 浏览 10 评论 0原文

我正在将 DateTime 转换为 OADate。我原本希望在将 OADate 转换回来时获得完全相同的 DateTime，但现在它只有毫秒分辨率，因此有所不同。

var a = DateTime.UtcNow;
double oadate = a.ToOADate();
var b = DateTime.FromOADate(oadate);
int compare = DateTime.Compare(a, b); 

//Compare is not 0; the date times are not the same

来自 a 的刻度：634202170964319073

来自 b 的刻度：634202170964310000

OADate 双精度：40437.290467951389

这是什么原因？ DateTime 的分辨率显然足够好。

原文

I'm converting a DateTime to OADate. I was expecting to get the exact same DateTime when converting the OADate back, but now it has only millisecond resolution, and is therefore different.

var a = DateTime.UtcNow;
double oadate = a.ToOADate();
var b = DateTime.FromOADate(oadate);
int compare = DateTime.Compare(a, b); 

//Compare is not 0; the date times are not the same

Ticks from a: 634202170964319073

Ticks from b: 634202170964310000

The OADate double: 40437.290467951389

What is the reason for this? The resolution of DateTime is clearly good enough.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泅人 2024-09-26 19:16:36

我认为这是一个很好的问题。（我刚刚发现它。）

除非您使用的日期非常接近 1900 年，否则 DateTime 将比 OA 日期更高精度。但由于某些晦涩的原因，DateTime 结构的作者只是喜欢在 DateTime 和其他内容之间转换时截断到最接近的整毫秒。不用说，这样做会在没有充分理由的情况下失去很多精度。

这是一个解决方法：

static readonly DateTime oaEpoch = new DateTime(1899, 12, 30);

public static DateTime FromOADatePrecise(double d)
{
  if (!(d >= 0))
    throw new ArgumentOutOfRangeException(); // NaN or negative d not supported

  return oaEpoch + TimeSpan.FromTicks(Convert.ToInt64(d * TimeSpan.TicksPerDay));
}

public static double ToOADatePrecise(this DateTime dt)
{
  if (dt < oaEpoch)
    throw new ArgumentOutOfRangeException();

  return Convert.ToDouble((dt - oaEpoch).Ticks) / TimeSpan.TicksPerDay;
}

现在，让我们考虑（根据您的问题）给出的 DateTime：

var ourDT = new DateTime(634202170964319073);
// .ToSting("O") gives 2010-09-16T06:58:16.4319073

任何 DateTime 的精度都是 0.1 µs。

在我们考虑的日期和时间附近，OA 日期的精度为：

Math.Pow(2.0, -37.0) 天，或大约 0.6286 µs

我们得出结论，在此区域有一个 DateTime 比 OA 日期精确（略多）六倍。

让我们使用上面的扩展方法将 ourDT 转换为 double

double ourOADate = ourDT.ToOADatePrecise();
// .ToString("G") gives 40437.2904679619
// .ToString("R") gives 40437.290467961888

现在，如果您使用以下方法将 ourOADate 转换回 DateTime上面的静态 FromOADatePrecise 方法，你得到

2010-09-16T06:58:16.4319072（以"O"格式编写）

与原始版本相比，我们看到这种情况下的精度损失为0.1 µs。我们预计精度损失在 ±0.4 µs 以内，因为该间隔长度为 0.8 µs，与前面提到的 0.6286 µs 相当。

如果我们采用另一种方式，从表示 OA 日期不太接近 1900 年的 double 开始，并且首先使用 FromOADatePrecise，并且然后 ToOADatePrecise，然后我们回到double，并且因为中间DateTime的精度优于对于 OA 日期，我们期望在这种情况下有一个完美的往返。另一方面，如果您以相同的顺序使用 BCL 方法 FromOADate 和 ToOADate，则极不可能获得良好的往返（除非 >double 我们开始时有一个非常特殊的形式）。

I think this is an excellent question. (I just discovered it.)

Unless you're operating with dates quite close to the year 1900, a DateTime will have a higher precision than an OA date. But for some obscure reason, the authors of the DateTime struct just love to truncate to the nearest whole millisecond when they convert between DateTime and something else. Needless to say, doing this throws away a lot of precision without good reason.

Here's a work-around:

static readonly DateTime oaEpoch = new DateTime(1899, 12, 30);

public static DateTime FromOADatePrecise(double d)
{
  if (!(d >= 0))
    throw new ArgumentOutOfRangeException(); // NaN or negative d not supported

  return oaEpoch + TimeSpan.FromTicks(Convert.ToInt64(d * TimeSpan.TicksPerDay));
}

public static double ToOADatePrecise(this DateTime dt)
{
  if (dt < oaEpoch)
    throw new ArgumentOutOfRangeException();

  return Convert.ToDouble((dt - oaEpoch).Ticks) / TimeSpan.TicksPerDay;
}

Now, let's consider (from your question) the DateTime given by:

var ourDT = new DateTime(634202170964319073);
// .ToSting("O") gives 2010-09-16T06:58:16.4319073

The precision of any DateTime is 0.1 µs.

Near the date and time we're considering, the precision of an OA date is:

Math.Pow(2.0, -37.0) days, or circa 0.6286 µs

We conclude that in this region a DateTime is more precise than an OA date by (just over) a factor six.

Let's convert ourDT to double using my extension method above

double ourOADate = ourDT.ToOADatePrecise();
// .ToString("G") gives 40437.2904679619
// .ToString("R") gives 40437.290467961888

Now, if you convert ourOADate back to a DateTime using the static FromOADatePrecise method above, you get

2010-09-16T06:58:16.4319072 (written with "O" format)

Comparing with the original, we see that the loss of precision is in this case 0.1 µs. We expect the loss of precision to be within ±0.4 µs since this interval has length 0.8 µs which is comparable to the 0.6286 µs mentioned earlier.

If we go the other way, starting with a double representing an OA date not too close to the year 1900, and first use FromOADatePrecise, and then ToOADatePrecise, then we get back to a double, and because the precision of the intermediate DateTime is superior to that of an OA date, we expect a perfect round-trip in this case. If, on the other hand, you use the BCL methods FromOADate and ToOADate in the same order, it is extremely improbable to get a good round-trip (unless the double we started with has a very special form).

回复收藏 0 原文

恋竹姑娘 2024-09-26 19:16:36

ToOADate 调用的静态方法明确地将刻度除以 10000，然后将结果存储在 long 中，从而删除任何亚毫秒信息

有谁知道在哪里可以找到 OADate 格式的规范？

    private static double TicksToOADate(long value)
    {
        if (value == 0L)
        {
            return 0.0;
        }
        if (value < 0xc92a69c000L)
        {
            value += 0x85103c0cb83c000L;
        }
        if (value < 0x6efdddaec64000L)
        {
            throw new OverflowException(Environment.GetResourceString("Arg_OleAutDateInvalid"));
        }
        long num = (value - 0x85103c0cb83c000L) / 0x2710L;
        if (num < 0L)
        {
            long num2 = num % 0x5265c00L;
            if (num2 != 0L)
            {
                num -= (0x5265c00L + num2) * 2L;
            }
        }
        return (((double)num) / 86400000.0);
    }

The static method called by ToOADate clearly divides the ticks by 10000 and then stores the result in a long, thus removing any sub millisecond info

Does anyone know where to find the specs of the OADate format?

    private static double TicksToOADate(long value)
    {
        if (value == 0L)
        {
            return 0.0;
        }
        if (value < 0xc92a69c000L)
        {
            value += 0x85103c0cb83c000L;
        }
        if (value < 0x6efdddaec64000L)
        {
            throw new OverflowException(Environment.GetResourceString("Arg_OleAutDateInvalid"));
        }
        long num = (value - 0x85103c0cb83c000L) / 0x2710L;
        if (num < 0L)
        {
            long num2 = num % 0x5265c00L;
            if (num2 != 0L)
            {
                num -= (0x5265c00L + num2) * 2L;
            }
        }
        return (((double)num) / 86400000.0);
    }

回复收藏 0 原文