Created
May 2, 2023 18:38
-
-
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)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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