PostgreSQL - 按UUID版本1时间戳排序

Question

我正在使用UUID版本1作为主键。我想按照UUID v1的时间戳进行排序。目前，如果我执行类似以下操作：

SELECT id, title 
FROM table 
ORDER BY id DESC;

PostgreSQL没有按照UUID时间戳对记录进行排序，而是按照UUID字符串表示进行排序，在我的情况下导致了意外的排序结果。

我有什么遗漏的吗？或者在PostgreSQL中没有内置的方法可以做到这一点？

- user232343

---

你尝试过像这样的语句吗？select id :: timestamp，title from table order by id :: timestamp desc - Santhucool

将程序相关的以下内容从英语翻译成中文：返回仅翻译文本：给我一个错误。 错误：无法将uuid类型转换为时间戳 - user232343

必须使用 uuid v1 吗？我尝试过 uuid_generate_v4，并且仅尝试了您的查询，它可以正常工作。 - Santhucool

Answer 1

时间戳是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));

- Clodoaldo Neto

---

如果您只想按“原始”时间戳排序（而不关心将其转换为Unix纪元时间戳），该怎么办？ - Robin Jonsson