Skip to content

Instantly share code, notes, and snippets.

@ShawnMilo

ShawnMilo/ksuid_to_timestamp.sql

Created May 2, 2023 18:38
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ShawnMilo/cef50b8c740af4429bcb88842ea3389e to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ShawnMilo/cef50b8c740af4429bcb88842ea3389e to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ShawnMilo created this gist May 2, 2023.
    34 changes: 34 additions & 0 deletions ksuid_to_timestamp.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,34 @@
    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;