PostgreSQL-按UUID v1时间戳排序 [英] Postgresql - sort by UUID v1 timestamp
问题描述
我正在使用UUID v1作为主键。我想对UUID v1时间戳进行排序。现在,如果我执行
I am using UUID v1 as the primary key. I would like to sort on UUID v1 timestamp. Right now if I do something like
select id, title
from table
order by id desc
Postgresql不会按UUID时间戳对记录进行排序,而是按UUID字符串表示对字符串进行排序,最终导致意外的排序结果
Postgresql does not sort records by UUID timestamp, but by UUID string representation, which ends up with unexpected sorting result in my case.
我错过了什么吗,或者在Postgresql中没有内置的方法来做到这一点?
Am I missing something, or there is not a built in way to do this in Postgresql?
推荐答案
时间戳是v1 UUID的一部分。自 1582-10-15 00:00
起,它以十六进制格式存储为数百纳秒。此函数提取时间戳记:
The timestamp is one of the parts of a v1 UUID. It is stored in hex format as hundreds nanoseconds since 1582-10-15 00:00
. This function extracts the timestamp:
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
是
在您的查询中:
select id, title
from t
order by uuid_v1_timestamp(id) desc
为提高性能,可以在该索引上创建索引:
To improve performance an index can be created on that:
create index uuid_timestamp_ndx on t (uuid_v1_timestamp(id));
这篇关于PostgreSQL-按UUID v1时间戳排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!