Skip to content

Instantly share code, notes, and snippets.

@ShawnMilo
Created May 2, 2023 18:38
Show Gist options
  • Save ShawnMilo/cef50b8c740af4429bcb88842ea3389e to your computer and use it in GitHub Desktop.
Save ShawnMilo/cef50b8c740af4429bcb88842ea3389e to your computer and use it in GitHub Desktop.
Convert a ksuid to a datetime/timestamp with time zone in PostgreSQL (plpgsql)
CREATE OR REPLACE FUNCTION ksuid_to_datetime(ksuid_base62 TEXT)
RETURNS TIMESTAMP WITH TIME ZONE AS $$
DECLARE
v_alphabet CHAR ARRAY[62] := ARRAY[
'0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
'U', 'V', 'W', 'X', 'Y', 'Z',
'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j',
'k', 'l', 'm', 'n', 'o', 'p', 'q', 'r', 's', 't',
'u', 'v', 'w', 'x', 'y', 'z'];
v_base62_length INTEGER := LENGTH(ksuid_base62);
v_numeric NUMERIC(50) := 0;
v_char CHAR;
v_index INTEGER;
v_seconds INTEGER;
v_epoch INTEGER := 1400000000; -- 2014-05-13T16:53:20Z
BEGIN
FOR i IN 1..v_base62_length LOOP
v_char := SUBSTRING(ksuid_base62 FROM i FOR 1);
v_index := -1;
FOR j IN 1..62 LOOP
IF v_alphabet[j] = v_char THEN
v_index := j - 1;
EXIT;
END IF;
END LOOP;
v_numeric := v_numeric * 62 + v_index;
END LOOP;
v_seconds := ((v_numeric::numeric / (2::numeric ^ 128))::numeric)::INTEGER;
RETURN (TO_TIMESTAMP(v_epoch) AT TIME ZONE 'UTC' + (v_seconds || ' seconds')::INTERVAL) AT TIME ZONE 'UTC';
END;
$$ LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment