Cast or extract timestamp from v1 UUID in PostgreSQL

Question

I'm trying to extract the timestamp from a Version 1 UUID, naively wished this worked:

SELECT '3efe0a20-f1b3-11e3-bb44-14109fec739e'::uuid::timestamp;

Here is a quick example showing how to extract the time in Go, but I'm hoping PostgreSQL has something built-in rather than creating a one-off PL/pgSql function:
http://play.golang.org/p/XRCooLgfaG

Phallus

There's no possible way what you wrote could work. To be able to extract the time component cleanly you'd need to be able to get the uuid in integer form (split up into four words, stored in bigints because Pg doesn't have unsigned ints) then mask and shift them. The "chop up the string and hex decode" approach is made harder by PostgreSQL's lack of a nice built-in hex-decode function :-( – Barouche

@CraigRinger thanks for the tip, your comment "chop up the string and hex decode" set me on the right path – Phallus

Answer 1

I've tested this with uuid's from my database and it seems to work very well, even without the unsigned bigints

CREATE FUNCTION uuid_timestamp(id uuid) RETURNS timestamptz AS $$
  select TIMESTAMP WITH TIME ZONE 'epoch' +
      (((('x' || lpad(split_part(id::text, '-', 1), 16, '0'))::bit(64)::bigint) +
      (('x' || lpad(split_part(id::text, '-', 2), 16, '0'))::bit(64)::bigint << 32) +
      ((('x' || lpad(split_part(id::text, '-', 3), 16, '0'))::bit(64)::bigint&4095) << 48) - 122192928000000000) / 10000000 ) * INTERVAL '1 second';    
$$ LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;

a V1 uuid I created in the 2099 future!

select uuid_timestamp('6d248400-65b7-1243-a57a-14109fec739e');
uuid_timestamp     
------------------------
 2099-08-01 11:30:00-07
(1 row)

Phallus

/ 10000000::DOUBLE PRECISION for sub-second precision – Cannell

Answer 2

An alternative to @Krut's implementation that is quite a bit faster based in our testing:

CREATE OR REPLACE FUNCTION uuid_timestamp(uuid UUID) RETURNS TIMESTAMPTZ AS $$
DECLARE
  bytes bytea; 
BEGIN
  bytes := uuid_send(uuid);
  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;

Note, it will only do millisecond precision so you may want to tweak the function's "/ 10000 / 1000::double precision" bit to just "/ 10000000::double precision" instead.

Example:

=> select uuid_timestamp(uuid_generate_v1()), now();
       uuid_timestamp       |              now
----------------------------+-------------------------------
 2020-04-29 17:40:54.519+00 | 2020-04-29 17:40:54.518204+00
(1 row)

Also, it is assuming the input is a v1. If you attempt to give it something like a v4, expect to get weird answers or alter the function to RAISE if it isn't v1.

=> select uuid_timestamp(uuid_generate_v4());
       uuid_timestamp
----------------------------
 4251-12-19 17:38:34.866+00
(1 row)

Heedful

Answer 3

Here is a rough pl/pgsql implementation which cast (timestamp, clock_seq, macaddr) into a version 1 uuid.

-- Build UUIDv1 via RFC 4122. 
-- clock_seq is a random 14bit unsigned int with range [0,16384)
CREATE OR REPLACE FUNCTION form_uuid_v1(ts TIMESTAMPTZ, clock_seq INTEGER, mac MACADDR)
  RETURNS UUID AS $$
DECLARE
  t       BIT(60) := (extract(EPOCH FROM ts) * 10000000 + 122192928000000000) :: BIGINT :: BIT(60);
  uuid_hi BIT(64) := substring(t FROM 29 FOR 32) || substring(t FROM 13 FOR 16) || b'0001' ||
                     substring(t FROM 1 FOR 12);
BEGIN
  RETURN lpad(to_hex(uuid_hi :: BIGINT) :: TEXT, 16, '0') ||
         (to_hex((b'10' || clock_seq :: BIT(14)) :: BIT(16) :: INTEGER)) :: TEXT ||
         replace(mac :: TEXT, ':', '');
END
$$ LANGUAGE plpgsql;

-- Usage
select form_uuid_v1(now(), 666, '44:88:AA:DD:BB:88');

Luthanen