Search code examples
postgresql

How to extract timestamp from UUID v7?

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?

Solution

  • 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;