Skip to content

Instantly share code, notes, and snippets.

@ycku
Created July 25, 2021 16:21
Show Gist options
  • Save ycku/06288607ad4d96dbeee501ee7c4f1049 to your computer and use it in GitHub Desktop.
Save ycku/06288607ad4d96dbeee501ee7c4f1049 to your computer and use it in GitHub Desktop.
Extract timestamp from uuid
-- Source from: https://stackoverflow.com/questions/24178485/cast-or-extract-timestamp-from-v1-uuid-in-postgresql/61508178
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
) / 10000000::double precision
);
END
$$ LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
RETURNS NULL ON NULL INPUT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment