uuids

uuid.sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

CREATE OR REPLACE FUNCTION uuid_timestamp(uuid UUID) RETURNS TIMESTAMPTZ AS $$
DECLARE
  bytes bytea;
BEGIN
  bytes := uuid_send(uuid);
  if  (get_byte(bytes, 6) >> 4)::int2 != 1 then
    RAISE EXCEPTION 'UUID version is not 1';
  end if;
  RETURN to_timestamp(
             (
                 (
                   (get_byte(bytes, 0)::bigint << 24) |
                   (get_byte(bytes, 1)::bigint << 16) |
                   (get_byte(bytes, 2)::bigint <<  8) |
                   (get_byte(bytes, 3)::bigint <<  0)
                 ) + (
                   ((get_byte(bytes, 4)::bigint << 8 |
                   get_byte(bytes, 5)::bigint)) << 32
                 ) + (
                   (((get_byte(bytes, 6)::bigint & 15) << 8 | get_byte(bytes, 7)::bigint) & 4095) << 48
                 ) - 122192928000000000
             ) / 10000 / 1000::double precision
         );
END
$$ LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
RETURNS NULL ON NULL INPUT;

create table test(id uuid);
select create_hypertable('test', 'id', time_partitioning_func=>'uuid_timestamp', chunk_time_interval=>'1 minute'::interval);
insert into test(id) values (uuid_generate_v1 ());

-- This will do correct chunk pruning
explain select * from test where id = 'fe259b36-46d8-11ee-a7f8-3af4df3541b1';