我在用UUID版本1 https://en.wikipedia.org/wiki/Universally_unique_identifier#Version_1_(date-time_and_MAC_address)作为主键。我想按 UUID v1 时间戳进行排序。现在如果我做这样的事情:
SELECT id, title
FROM table
ORDER BY id DESC;
PostgreSQL 不按 UUID 时间戳对记录进行排序,而是按 UUID 字符串表示形式对记录进行排序,在我的情况下,这最终会产生意外的排序结果。
我是否遗漏了什么,或者 PostgreSQL 中没有内置的方法来执行此操作?
时间戳是 v1 UUID 的一部分。它以十六进制格式存储为数百纳秒,因为1582-10-15 00:00
。该函数提取时间戳:
create or replace function uuid_v1_timestamp (_uuid uuid)
returns timestamp with time zone as $$
select
to_timestamp(
(
('x' || lpad(h, 16, '0'))::bit(64)::bigint::double precision -
122192928000000000
) / 10000000
)
from (
select
substring (u from 16 for 3) ||
substring (u from 10 for 4) ||
substring (u from 1 for 8) as h
from (values (_uuid::text)) s (u)
) s
;
$$ language sql immutable;
select uuid_v1_timestamp(uuid_generate_v1());
uuid_v1_timestamp
-------------------------------
2016-06-16 12:17:39.261338+00
122192928000000000
是公历开始时间与 Unix 时间戳之间的间隔。
在您的查询中:
select id, title
from t
order by uuid_v1_timestamp(id) desc
为了提高性能,可以在其上创建索引:
create index uuid_timestamp_ndx on t (uuid_v1_timestamp(id));
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)