tipos - Postgresql-ordenar por indicación de fecha y hora de UUID v1

postgresql wikipedia

Question

Estoy usando UUID v1 como clave principal. Me gustaría ordenar en UUID v1 timestamp. Ahora mismo si hago algo como

select id, title 
from table 
order by id desc

Postgresql no ordena los registros por la marca de tiempo UUID, sino por la representación de cadena UUID, que termina con un resultado de clasificación inesperado en mi caso.

¿Me estoy perdiendo algo, o no hay una forma de hacerlo en Postgresql?

Answer 1

La marca de tiempo es una de las partes de un UUID v1. Se almacena en formato hexadecimal como cientos nanosegundos desde 1582-10-15 00:00 . Esta función extrae la marca de tiempo:

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 es el intervalo entre el inicio del calendario gregoriano y la marca de tiempo Unix.

En tu consulta:

select id, title
from t
order by uuid_v1_timestamp(id) desc

Para mejorar el rendimiento, se puede crear un índice sobre eso:

create index uuid_timestamp_ndx on t (uuid_v1_timestamp(id));