Shulou(Shulou.com)06/01 Report--

In SQL Server's internal storage, dates and times are not stored as strings, but as integers. Use a specific format to distinguish the offset of the date part and the time part, and restore the real data through the base date and time.

First, the internal storage of DateTime

The SQL Server storage engine stores the DateTime type as 2 int32 types, a total of 8 bytes, and the first int32 integer (the first 4 bytes) stores the offset of the date from the base date (1900-01-01). The base date is 1900-01-01. When the current 4-byte is 0, the date is January 1, 1900. The second int32 integer (the last 4 bytes) stores the clock tick after midnight, each ticking is 1-300 seconds, with an accuracy of 3.33 milliseconds (0.00333 seconds, 3.33ms), so there may be a ticking time error in the time that DateTime can represent.

The internal storage format of DateTime, expressed in hexadecimal: DDDDTTTT

DDDD: occupies 2 bytes, indicating the offset from the base date TTTT: occupies two bytes, indicating the number of ticks always after midnight

For example, convert the DateTime type to 8-byte hexadecimal for the following date and time, with one byte for every two digits:

Declare @ dt datetime = '2015-05-07 10:05:23.187'select convert (varbinary (8), @ dt) as date_time_binary--output 0x0000A49100A6463C

1, split date and time

Split the bytes in the binary format of time into two parts: the first four bytes represent date and the last four bytes represent time. The result is as follows:

Declare @ dt datetime = '2015-05-07 10:05:23.187'select substring (convert (varbinary (8), @ dt), 1,4) as date_binary, cast (substring (convert (varbinary (8), @ dt), 1,4) as int) as date_int, substring (convert (varbinary (8), @ dt), 5,4) as time_binary, cast (substring (convert (varbinary (8), @ dt), 5,4) as int as time_int)

2, restore date and time by offset

Restore the integer to the original date and time through the base time and offset:

Declare @ Time time='00:00:00.000'declare @ Date date='1900-01-01'select dateadd (day, 42129, @ Date) as originl_date, dateadd (ms,10896956*10/3, @ Time) as original_time

Second, the internal storage of DateTime2

The DateTime2 (n) data type stores the date and time, which is an upgraded version of DateTime. Because the precision of fractional second n can be set independently, its storage size (Storage Size) is not fixed. The relationship between the storage space occupied by DateTime2 (n) and the precision of fractional seconds is:

DateTime2 (n) the first byte stored internally is stored with precision n, and subsequent bytes are used to store the values of date and time. When the precision of fractional second n < 3, the total storage space is 1B (precision) + 6B (data); when the precision of fractional second n is 3-4, the total storage space is 1B (precision) + 7B (data); when the precision of fractional second n is 5-7, the total storage space is 1B (precision) + 8B (data), the maximum fractional second precision is 7, and the default value is 7.

1, binary reverse order

Before exploring the internal storage of DateTime2 (n), take a look at the "small-end" and "big-end" formats of byte storage:

Big end format: the low bit of data is stored in the high address of memory, while the high bit of data is saved in the low address of memory; the small end format means that the low bit of data is stored in the low address of memory, and the high bit of data is stored in the high address of memory.

For example, if the memory address is status on the left and high bit on the right, for the number 275, use two bytes to store:

If you use the large-end format: the byte sequence is 0x0113 if you use the small-end format: the byte sequence is 0x1301

The internal storage format of DateTime2 (n) uses a small-end format, which is suitable for CPU operations.

2the storage format of DateTime2

The internal storage format of DateTime2 (n) is:

The precision of the first byte is n, and the 0001 bytes record the number of days after the base date 01-01-01, in small-end format. The remaining bytes in the middle record the number of time unit intervals (time unit interval,TUI) that have elapsed after midnight, in small-end format.

TUI is controlled by precision, and each TUI is one second to the nth power of 10, that is,

For DateTime2 (7), TUI is 100ns; for DateTime2 (6), TUI is 1 microsecond (= 1000ns); for DateTime2 (5), TUI is 10 microseconds; for DateTime2 (4), TUI is 100 microseconds; for DateTime2 (3), TUI is 1ms (1 millisecond = 1000 microseconds)

To facilitate the operation, the byte stream of DateTime2 (n) is arranged in reverse order: the first three bytes represent the number of days, the last byte represents precision, and the remaining bytes in the middle represent the number of TUI. For example, for DateTime2 (7), after processing in reverse byte stream, the storage space is nine bytes: the first three bytes are stored how many days after the base date 0001-01-01, the last bit is precision n, and the middle five bytes indicate how many TUI there are since midnight.

2, convert DateTime2 to binary storage

The DateTime2 is converted into binary storage and processed in reverse order. The precision of DateTime2 (3) is 3, and the storage space is 8 bytes. The last three bytes record the number of days after the base date 0001-01-01, and the first three bytes indicate how many TUI there are since midnight.

Declare @ dt datetime2 (3) = '2015-05-07 10:05:23.187'declare @ dt_bi varbinary (max) = convert (varbinary (max), @ dt) select @ dt_bi as date_time_binary, convert (varbinary (max), reverse (@ dt_bi)) as reverse_binary

Split the binary values into the components of DateTime2 (3):

Declare @ dt datetime2 (3) = '2015-05-07 10:05:23.187'declare @ dt_bi varbinary (max) = convert (varbinary (max), @ dt) declare @ dt_bi_littleEnd varbinary (max) select @ dt_bi_littleEnd=convert (varbinary (max), reverse (@ dt_bi)) select substring (convert (varbinary (8), @ dt_bi_littleEnd), 1,3) as date_binary, cast (substring (convert (varbinary (8), @ dt_bi_littleEnd), 1 3) as int) as date_int, substring (convert (varbinary (8), @ dt_bi_littleEnd), 4,4) as time_binary, cast (substring (convert (varbinary (8), @ dt_bi_littleEnd), 4,4) as int) as time_int, substring (convert (varbinary (8), @ dt_bi_littleEnd), 8,1) as precision_binary, cast (substring (varbinary (8), @ dt_bi_littleEnd), 8,1) as int as precision_int)

3, using offset and datum to restore the original value

With an offset, you can add an offset to the base date and time to get the original value:

Declare @ Time time='00:00:00.000'declare @ Date date='0001-01-01'select dateadd (day, 735724, @ Date) as originl_date, dateadd (ms,36323187, @ Time) as original_time

Reference documentation:

What is the SQL Server 2008 DateTime2 Internal Structure?

How to Get SQL Server Dates and Times Horribly Wrong

Summary

The above is the internal storage of the SQL Server date and time introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.