BigQueryでUUIDv7形式の文字列をTIMESTAMP型に変換する方法

IETFの資料によると、UUIDv7の先頭48bitがUnix timestampをミリ秒で表したもの

 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

出展:

なので、雑に実装するならその部分を取り出してTIMESTAMP型に変換すればOKで、こんな感じ。

timestamp_millis(cast('0x' || left(replace(uuid, '-', ''), 12) as int64))

しかし、この方法ではUUIDv7以外のものを入れた場合にエラーになることがあるので、最初に有効なUUIDv7かどうかを正規表現でチェックする。

またIETFの資料に立ち返ると、verの部分の二進数表示は 0111　で、varの部分は 10 であることが分かるので、UUIDv7かどうかを判断する正規表現はこうなる。

^[0-9a-f]{8}-[0-9a-f]{4}-7[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$

あとは、組み合わせてこうなる。

create temporary function convert_uuidv7_to_timestamp(uuid string) as (
  if(
    regexp_contains(uuid, r'^[0-9a-f]{8}-[0-9a-f]{4}-7[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'),
    timestamp_millis(cast('0x' || left(replace(uuid, '-', ''), 12) as int64)),
    null
  )
);