Extracting timestamps from UUIDv6

If you have a UUIDv6 in Clickhouse such as 1ec90684-5c11-62f8-8d4a-a9fc15074a15 and you want to convert it to an epoch timestamp, you can use the following function:

select
    (reinterpretAsUInt32(substr(reinterpretAsString(toUUID('1ec90684-5c11-62f8-8d4a-a9fc15074a15')) AS uuid, 5, 4)) * pow(2, 28)
    + reinterpretAsUInt16(substr(uuid, 3, 2)) * pow(2,2)
    + reinterpretAsUInt16(substr(uuid, 1, 2)) - 0x6000
    - 0x01b21dd213814000) / 10000000;

This looks pretty weird, so let’s break it down:

reinterpretAsString(toUUID('1ec90684-5c11-62f8-8d4a-a9fc15074a15')) AS uuid

First we take the UUID and convert it to a string. If you look at the hex version of this you see it’s been reversed according to little-endian rules to read F862115C8406C91E154A0715FCA94A8D.

We then take the various sections of this and convert them to numbers according to the spec in order to get a 60-bit timestamp.

After this, we subtract the UUID version from it, as the first 4 bits of the time_low section contain the UUID version (6), hence the - 0x6000.

However the 60-bit timestamp is in 100 nanosecond increments since 00:00:00.00, 15 October 1582 (the start of the Gregorian calendar). Looking on google, the magic number of 100-nanosecond increments between then and the unix epoch (1 Jan 1970) is 0x01b21dd213814000, so we subtract this.

Then finally to convert to seconds we divide by 10 million (10000000)