PostgreSQL - 按 UUID 版本 1 时间戳排序 [英] PostgreSQL - sort by UUID version 1 timestamp

问题描述

我使用 UUID 版本 1 作为主键.我想对 UUID v1 时间戳进行排序.现在，如果我这样做:

I am using UUID version 1 as the primary key. I would like to sort on UUID v1 timestamp. Right now if I do something like this:

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 是公历开始和 Unix 时间戳之间的间隔.

122192928000000000 is the interval between the start of the Gregorian calendar and the Unix timestamp.

在您的查询中:

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 版本 1 时间戳排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！