Synapse column store datetime encoding

Question

I have a table in an Azure Synapse database. This table has a clustered columnstore index. It has a datetime column. The minimum value in this column is 2001-03-27 00:00:00 and the maximum is 2022-12-31 00:00:00.

When I look at sys.pdw_nodes_column_store_segments (docs). This column has encoding_type "2 = VALUE_HASH_BASED - non-string/binary column with common values in dictionary" for all segments.

The smallest min_data_id and largest max_data_id are 158806415769600 and 192947110805504 respectively.

How do the values in the data_id columns relate to the source data in the table?

Answer 1

DECLARE 
    @dt1 datetime = '2001-03-27 00:00:00',
    @dt2 datetime = '2022-12-31 00:00:00';

SELECT 
    dt1 = CONVERT(bigint, CONVERT(binary(8), @dt1)),
    dt2 = CONVERT(bigint, CONVERT(binary(8), @dt2));

dt1	dt2
158806415769601	192947110805504

The first four bytes are the number of days since '1900-01-01'.
The second four bytes are the number of 1/300s intervals since midnight.