如何使用Python将二进制数据从SQLServer转换为人类可读日期和时间

发布于 2025-02-07 19:37:30 字数 908 浏览 3 评论 0原文

我在原始二进制文件（数据库的一部分）中有一个值，我想转换为Python格式，该格式可以由人类解释。这是法医雕刻程序的一部分。我可以使用此SQL句子转换8个字节值（您将在GMT+2和GMT中看到一个日期）

选择cast（0x0000aae940101039c4a作为dateTime），cast（0x000000aeAeae2a6ca as DateTime as DateTime as DateTime

）

2022-05-13 15：45：12.780 2022-05-13 13：45：12.780

我试图使用dcode v5.5转换二进制值-detective.net/dcode/“ rel =“ nofollow noreferrer”> https://www.digital-detective.net/dcode/ ），但找不到任何格式符合以前的SQL句子的输出（我已经检查了它在我想雕刻的数据库中是否正确）。

有人知道如何在Python中执行转换吗？

我想我只需要这段时间表示的起源，以及每一时的时间。比较两个时间戳完全分开了2个小时，您可以看到我不知道该解释的“ 300”。有点1/300秒吗？

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

我需要雕刻数据库的属性：

原文

I have a value in a raw binary file (part of a database) and I want to convert to a Python format which can be interpreted by a human being. This is part of a forensic carving procedure.
I can convert 8 byte values using this SQL sentence (you will see a date in GMT+2 and in GMT)

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

which returns

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

I have tried to convert the binary value with DCODE v5.5 (https://www.digital-detective.net/dcode/) but can't find any format meeting the output of the previous SQL sentence (I have checked that it is right in the database I'm trying to carve).

Do anyone know how to perform the conversion in Python?

I imagine I just need the origin of this time representation and how much time is every bit. Comparing two timestamps separated exactly 2 hours you can see a "300" that I don't know how to interpret. Is a bit 1/300 seconds?

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

Those ere the properties of the database I need to carve:

分享到QQ

分享到微博

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

发布评论

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

还在原地等你 2025-02-14 19:37:30

简短版本

旧版datetime类型存储日期为64位浮点偏移，从1900-01-01

floatValue=struct.unpack('<d',bytes)[0]
OLE_TIME_ZERO = datetime.datetime(1900, 01, 01, 0, 0, 0)
date=OLE_TIME_ZERO + datetime.timedelta(days=floatValue)

较新的类型不使用该格式。

Excel处理库，例如OpenPyXl提供函数，可转换OA/串行日期，例如 openpyxl.utils.datetime.from_excel.from_excel

长解释

premacy dateTime dateTime 在SQL Server使用中存储格式在Excel，Visual Basic和所有桌面应用程序中也用于1990年代末和2000年代初支持OLE/COM自动化的所有桌面应用程序。这是一个64位浮点数（称为excel中的串行日期）整体部分是自1899-12-30以来的偏移，而分数部分是一天中的时间。除非是1899-12-31或1900-01-01。

回到Excel发行时，Lotus 1-2-3是最受欢迎的电子表格和Defatso标准，并且错误地认为1900年是LEAP年。为了确保兼容性，Excel采用了相同的错误。采用的VBA试图修复错误和确保公式与Excel和Lotus产生相同的结果，因此请使用1899-12-30作为基础。

SQL Server团队不在乎该错误，而是使用逻辑1900-01-01。

本质上，此值是timedelta。在Python中，您可以通过将其作为days参数传递到timeDELTA，然后将其添加到基础1900-01-01：

OLE_TIME_ZERO = datetime.datetime(1900, 01, 01, 0, 0, 0)
date=OLE_TIME_ZERO + datetime.timedelta(days=floatValue)

要获取一个：获得一个从一个字节数组中的64位浮动您可以使用 with 适当的格式a>。 64位浮点实际上是双重：

floatValue=struct.unpack('<d',bytes)[0]

警告

datetime是一种旧类型。 2005年推出的类型，date，time，dateTime2和dateTimeOffset具有不同的存储格式。 dateTime2和dateTimeOffset具有可变精度和变量大小。

Short Version

The legacy datetime type stores dates as a 64-bit floating point offset from 1900-01-01

floatValue=struct.unpack('<d',bytes)[0]
OLE_TIME_ZERO = datetime.datetime(1900, 01, 01, 0, 0, 0)
date=OLE_TIME_ZERO + datetime.timedelta(days=floatValue)

Newer types don't use that format though.

Excel handling libraries like openpyxl offer functions that convert OA/Serial dates like openpyxl.utils.datetime.from_excel

Long Explanation

The legacy datetime type in SQL Server uses the OLE Automation Date storage format that was also used in Excel, Visual Basic and all desktop applications that supported OLE/COM Automation in the late 1990s and early 2000s, before macro viruses. This is a 64-bit floating point number (called a Serial date in Excel) whose integral part is an offset since 1899-12-30 and fractional part is the time of day. Except when it's 1899-12-31 or 1900-01-01.

Back when Excel was released, Lotus 1-2-3 was the most popular spreadsheet and a defacto standard, and incorrectly considered 1900 a leap year. To ensure compatibility, Excel adopted the same bug. VBA adopted tried to both fix the bug and ensure formulas produced the same results as Excel and Lotus, so use 1899-12-30 as a base.

The SQL Server team didn't care about the bug and used the logical 1900-01-01 instead.

Essentially, this value is a timedelta. In Python, you can convert this float to a timedelta by passing it as the days parameter to timedelta, and add it to the base 1900-01-01:

OLE_TIME_ZERO = datetime.datetime(1900, 01, 01, 0, 0, 0)
date=OLE_TIME_ZERO + datetime.timedelta(days=floatValue)

To get a 64-bit float from an array of bytes you can use struct.unpack with the appropriate format string. A 64-bit float is actually a double:

floatValue=struct.unpack('<d',bytes)[0]

WARNING

datetime is a legacy type. The types introduced in 2005, date, time, datetime2 and datetimeoffset have a different storage format. datetime2 and datetimeoffset have variable precision and variable size.

回复收藏 0 原文

软糖 2025-02-14 19:37:30

对于将来的参考，我终于能够找到我在这篇文章中需要的数据： SQL Server中DateTime的内部表示是什么？

细节据说是不透明的，但是我在Web状态上发现的大多数资源（1），（2）以下内容：
前4个字节存储以来SQL Server的时代以来的天数（1900年1月1日）和
第二个4个字节存储了午夜后的壁虱数量，其中“ tick”为3.3毫秒。
前四个字节已签名（可以是正面的或负面的），这解释了为什么可以比时期早的日期更早。

此功能将进行转换：

def extr_datetime (bytes):
    days_off = int.from_bytes(bytes[4:8],byteorder='little', signed=True)
    ticks_off = int.from_bytes(bytes[0:4],byteorder='little', signed=True) / 300.0
    epoch = '1900/01/01 00:00:00'
    epoch_obj = datetime.strptime(epoch, '%Y/%m/%d %H:%M:%S')
    d = epoch_obj + timedelta(days=days_off) + timedelta(seconds=ticks_off)
    return d

For future reference, I was finally able to find the data I needed in this post: What is the internal representation of datetime in sql server?

The details are supposedly opaque, but most resources (1), (2) that I've found on the web state the following:
The first 4 bytes store the number of days since SQL Server's epoch (1st Jan 1900) and that
the second 4 bytes stores the number of ticks after midnight, where a "tick" is 3.3 milliseconds.
The first four bytes are signed (can be positive or negative), which explains why dates earlier than the epoch can be represented.
https://learn.microsoft.com/en-us/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16
Range: 1753-01-01 through 9999-12-31
Accuracy: 0.00333 second

This function will do the conversion:

def extr_datetime (bytes):
    days_off = int.from_bytes(bytes[4:8],byteorder='little', signed=True)
    ticks_off = int.from_bytes(bytes[0:4],byteorder='little', signed=True) / 300.0
    epoch = '1900/01/01 00:00:00'
    epoch_obj = datetime.strptime(epoch, '%Y/%m/%d %H:%M:%S')
    d = epoch_obj + timedelta(days=days_off) + timedelta(seconds=ticks_off)
    return d

回复收藏 0 原文

~没有更多了~