在Postgresql中从v1 uuid强制转换或提取timestamptz [英] Cast or extract timestamptz from v1 uuid in postgresql

问题描述

我正在尝试从V1 uuid中提取时间戳，天真地希望它能起作用：

I'm trying to extract the timestamp from a V1 uuid, naively wished this worked:

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

下面是一个简单的示例，显示了如何提取行进时间，但我希望Postgresql有内置的东西，而不是创建一个单一的pl / pgSql函数。 http://play.golang.org/p/XRCooLgfaG

Here is a quick example showing how to extract the time in go, but I'm hoping postgresql has something built-in rather than creating a one-off pl/pgSql function. http://play.golang.org/p/XRCooLgfaG

推荐答案

我已经使用数据库中的uuid对它进行了测试，即使没有未签名的bigint，它也可以很好地工作

I've tested this with uuid's from my database and it seems to work very well, even without the unsigned bigints

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！

a V1 uuid I created in the 2099 future!

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

这篇关于在Postgresql中从v1 uuid强制转换或提取timestamptz的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！