PostgreSQL - sort by UUID version 1 timestamp

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, titlefrom torder 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));