从 PostgreSQL 的 v1 UUID 中截取或提取时间戳

Question

我想从版本1 UUID中提取时间戳，尝试了以下方法：

SELECT '3efe0a20-f1b3-11e3-bb44-14109fec739e'::uuid::timestamp;

这里有一个快速的示例，展示了如何在Go中提取时间，但我希望PostgreSQL有内置的方法，而不是创建一个单独的PL/pgSql函数：
http://play.golang.org/p/XRCooLgfaG

- Krut

---

你所写的代码不可能起作用。要能够干净地提取时间组件，您需要能够以整数形式获取uuid（分成四个单词，存储在bigints中，因为Pg没有无符号整数），然后进行掩码和移位。由于PostgreSQL缺乏一个好的内置十六进制解码函数，所以“将字符串切割并进行十六进制解码”的方法变得更加困难 :-( - Craig Ringer

@CraigRinger 感谢您的提示，您的评论“将字符串分解并进行十六进制解码”让我找到了正确的方法。 - Krut

Answer 1

我已经使用来自我的数据库的UUID进行了测试，似乎即使没有无符号大整数，它也能很好地运行。

CREATE FUNCTION uuid_timestamp(id uuid) RETURNS timestamptz AS $$
  select TIMESTAMP WITH TIME ZONE 'epoch' +
      (((('x' || lpad(split_part(id::text, '-', 1), 16, '0'))::bit(64)::bigint) +
      (('x' || lpad(split_part(id::text, '-', 2), 16, '0'))::bit(64)::bigint << 32) +
      ((('x' || lpad(split_part(id::text, '-', 3), 16, '0'))::bit(64)::bigint&4095) << 48) - 122192928000000000) / 10000000 ) * INTERVAL '1 second';    
$$ LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;

我在2099年创造的一个V1 uuid！

select uuid_timestamp('6d248400-65b7-1243-a57a-14109fec739e');
uuid_timestamp     
------------------------
 2099-08-01 11:30:00-07
(1 row)

- Krut

---

/ 10000000::DOUBLE PRECISION 可以实现亚秒级精度。 - deej

Answer 2

根据我们的测试，与@Krut实现相比较快的一种替代方案：

CREATE OR REPLACE FUNCTION uuid_timestamp(uuid UUID) RETURNS TIMESTAMPTZ AS $$
DECLARE
  bytes bytea; 
BEGIN
  bytes := uuid_send(uuid);
  RETURN to_timestamp(
             (
                 (
                   (get_byte(bytes, 0)::bigint << 24) |
                   (get_byte(bytes, 1)::bigint << 16) |
                   (get_byte(bytes, 2)::bigint <<  8) |
                   (get_byte(bytes, 3)::bigint <<  0)
                 ) + (
                   ((get_byte(bytes, 4)::bigint << 8 |
                   get_byte(bytes, 5)::bigint)) << 32
                 ) + (
                   (((get_byte(bytes, 6)::bigint & 15) << 8 | get_byte(bytes, 7)::bigint) & 4095) << 48
                 ) - 122192928000000000
             ) / 10000 / 1000::double precision
         );
END
$$ LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
RETURNS NULL ON NULL INPUT;

注意，它只能进行毫秒级精度，因此您可能需要调整函数中的“/ 10000 / 1000 :: double precision”部分，改为只使用“/ 10000000 :: double precision”。

示例：

=> select uuid_timestamp(uuid_generate_v1()), now();
       uuid_timestamp       |              now
----------------------------+-------------------------------
 2020-04-29 17:40:54.519+00 | 2020-04-29 17:40:54.518204+00
(1 row)

此外，它假设输入是 v1 版本。如果您尝试使用类似 v4 的输入，则应该预期会得到奇怪的答案，或者更改函数使其在不是 v1 版本时引发错误。

=> select uuid_timestamp(uuid_generate_v4());
       uuid_timestamp
----------------------------
 4251-12-19 17:38:34.866+00
(1 row)

- vincentjames501

Answer 3

这里是一个粗略的 pl/pgsql 实现，将 (timestamp, clock_seq, macaddr) 转换成版本 1 的 uuid。

-- Build UUIDv1 via RFC 4122. 
-- clock_seq is a random 14bit unsigned int with range [0,16384)
CREATE OR REPLACE FUNCTION form_uuid_v1(ts TIMESTAMPTZ, clock_seq INTEGER, mac MACADDR)
  RETURNS UUID AS $$
DECLARE
  t       BIT(60) := (extract(EPOCH FROM ts) * 10000000 + 122192928000000000) :: BIGINT :: BIT(60);
  uuid_hi BIT(64) := substring(t FROM 29 FOR 32) || substring(t FROM 13 FOR 16) || b'0001' ||
                     substring(t FROM 1 FOR 12);
BEGIN
  RETURN lpad(to_hex(uuid_hi :: BIGINT) :: TEXT, 16, '0') ||
         (to_hex((b'10' || clock_seq :: BIT(14)) :: BIT(16) :: INTEGER)) :: TEXT ||
         replace(mac :: TEXT, ':', '');
END
$$ LANGUAGE plpgsql;

-- Usage
select form_uuid_v1(now(), 666, '44:88:AA:DD:BB:88');

- Vonng