PostgreSQL - sort by UUID version 1 timestamp

Question

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 does not sort records by UUID timestamp, but by UUID string representation, which ends up with unexpected sorting result in my case.

Am I missing something, or there is not a built in way to do this in PostgreSQL?

Answer 1

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 is the interval between the start of the Gregorian calendar and the Unix timestamp.

In your query:

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));