如何使用Python将SQL Server二进制datetime转换为可读日期时间

• 场景：从构成数据库的原始二进制文件中提取到8字节时间值，属于取证雕刻流程的一部分，需要将其转换为Python可识别的人类可读日期格式。
• 已知验证规则：通过SQL语句可直接将该二进制值转换为datetime类型，分别得到GMT+2、GMT时区的对应日期：

  SELECT CAST(0x0000ae9401039c4a AS datetime), CAST(0x0000ae9400e2a6ca AS datetime)
  

  语句执行返回结果：

  2022-05-13 15:45:12.780  2022-05-13 13:45:12.780
  

• 已尝试操作：使用DCODE v5.5工具转换该二进制值，未得到和上述SQL输出匹配的结果，已确认待雕刻数据库中的原始值准确无误。
• 前期推导过程：
  1. 该时间格式的转换核心是明确时间原点、最小计量单位对应的时间长度
  2. 对比上述相差2小时的两个时间戳，计算得到差值系数为300，初步猜测1个时间单位对应1/300秒，计算代码如下：

     >>> t1=0xae9401039c4a
     >>> t2=0xae9400e2a6ca
     >>> t1-t2
     2160000
     >>> (t1-t2)/(2*3600)
     300.0
     

  3. 参考该datetime类型的公开属性说明：8字节长度分为两段独立存储日期、时间信息。

你计算得到的300系数完全匹配微软SQL Server的datetime类型存储规范：

• 8字节值拆分为两个4字节整数：
  • 前4字节为有符号整数，存储距离时间原点1900-01-01 00:00:00的天数偏移
  • 后4字节为无符号整数，存储当日零点之后的时间偏移，最小计量单位为1/300秒（约3.33毫秒），这就是差值计算得到300系数的原因。
• 示例校验：两个测试值的前4字节均为0x0000ae94，换算为十进制是44692，从1900-01-01累加44692天恰好为2022-05-13，和SQL返回的日期完全匹配；后4字节差值2160000除以300等于7200秒即2小时，和两个结果的时区差完全吻合。

import struct
from datetime import datetime, timedelta

# 定义SQL Server datetime类型的时间原点
DATETIME_EPOCH = datetime(1900, 1, 1)

def bin_to_mssql_datetime(raw_8byte: bytes) -> datetime:
    """
    将8字节原始二进制值转换为Python datetime对象
    常规数据库文件中该值存储为小端序
    """
    # 按小端序拆分出天数偏移、时间偏移两个4字节无符号整数
    day_offset, time_offset = struct.unpack("<LL", raw_8byte)
    return DATETIME_EPOCH + timedelta(days=day_offset, seconds=time_offset / 300)

# 测试用例
if __name__ == "__main__":
    # 对应GMT+2时间值0x0000ae9401039c4a的小端序字节
    test_val1 = bytes.fromhex("4a9c030194ae0000")
    # 对应GMT时间值0x0000ae9400e2a6ca的小端序字节
    test_val2 = bytes.fromhex("caa6e20094ae0000")
    print(bin_to_mssql_datetime(test_val1))  # 输出：2022-05-13 15:45:12.780000
    print(bin_to_mssql_datetime(test_val2))  # 输出：2022-05-13 13:45:12.780000

提示：如果从二进制文件中读取的值转换后日期偏差极大，可将struct.unpack的格式参数从<LL改为>LL切换为大端序解析，绝大多数场景下SQL Server存储的该类型值为小端序。

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