UUID v7 is defined as:
The first 48 bits are a big-endian unsigned number of milliseconds since the Unix epoch. The next four bits are the version bits (0111), followed by 12 bits of pseudo-random data.
How do I extract the timestamp from UUID v7 string?
This works:
SELECT to_timestamp(x'0187296000797352b8a70b7088b46d0f'::bit(48)::bigint / 1000);
which gives "2023-03-28 17:50:52"
This could be abstracted into:
CREATE OR REPLACE FUNCTION extract_timestamp_from_uuid_v7(uuid_v7 UUID)
RETURNS TIMESTAMP AS $$
SELECT to_timestamp(('x'||replace(uuid_v7::text, '-', ''))::bit(48)::bigint / 1000) AS result;
$$ LANGUAGE sql IMMUTABLE;