EF Core+MySQL：如何查询经ToBinary转换的DateTime值

核心原理 #

EF Core的DateTimeToBinaryConverter依赖DateTime.ToBinary()生成64位整数：

• 最高位（第63位）是时区标志位：1对应Local/Unspecified类型的DateTime（存储为负数），0对应UTC类型（存储为正数）
• 剩余63位为Ticks值：以100纳秒为单位，基准时间为0001-01-01 00:00:00

转换方法 #

1. 基础转换（不考虑时区）

通过位运算剥离时区标志，提取纯Ticks值后用DATE_ADD转换为MySQL Datetime：

SELECT
  your_binary_column,
  DATE_ADD(
    '0001-01-01 00:00:00',
    INTERVAL (your_binary_column & 0x7FFFFFFFFFFFFFFF) / 10000000 SECOND
  ) AS converted_datetime
FROM your_table;

• 0x7FFFFFFFFFFFFFFF：63位全1的十六进制值，用于屏蔽最高位的时区标志，提取纯Ticks
• / 10000000：将Ticks转换为秒（1 Tick = 100纳秒 = 0.0000001秒）

2. 时区调整（针对Local类型的负数）

如果存储的是Local类型的DateTime（值为负），需根据应用时区调整。例如，应用时区为东8区，转换为UTC时间：

SELECT
  your_binary_column,
  CASE
    -- 负数代表Local/Unspecified类型，减去东8区时差得到UTC
    WHEN your_binary_column < 0 THEN DATE_SUB(
      DATE_ADD('0001-01-01 00:00:00', INTERVAL (your_binary_column & 0x7FFFFFFFFFFFFFFF) / 10000000 SECOND),
      INTERVAL 8 HOUR
    )
    -- 正数代表UTC类型，直接转换
    ELSE DATE_ADD('0001-01-01 00:00:00', INTERVAL (your_binary_column & 0x7FFFFFFFFFFFFFFF) / 10000000 SECOND)
  END AS converted_utc_datetime
FROM your_table;

验证示例 #

在C#生成测试值，再用SQL验证转换结果：

// 生成Local类型的DateTime（ToBinary()返回负数）
var localDt = new DateTime(2024, 5, 20, 14, 30, 0, DateTimeKind.Local);
long binaryValue = localDt.ToBinary();
Console.WriteLine(binaryValue); // 输出负数

将binaryValue代入SQL查询，转换结果应与localDt一致（或调整时区后匹配）。

内容的提问来源于stack exchange，提问作者dow