PostgreSQL base36 time functions

base36_decode.sql

-- FUNCTION: public.base36_decode(character varying)

-- DROP FUNCTION public.base36_decode(character varying);

CREATE OR REPLACE FUNCTION public.base36_decode(
	base36 character varying)
    RETURNS bigint
    LANGUAGE 'plpgsql'

    COST 100
    IMMUTABLE 
    ROWS 0
AS $BODY$

        DECLARE
			a char[];
			ret bigint;
			i int;
			val int;
			chars varchar;
		BEGIN
		chars := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
 
		FOR i IN REVERSE char_length(base36)..1 LOOP
			a := a || substring(upper(base36) FROM i FOR 1)::char;
		END LOOP;
		i := 0;
		ret := 0;
		WHILE i < (array_length(a,1)) LOOP		
			val := position(a[i+1] IN chars)-1;
			ret := ret + (val * (36 ^ i));
			i := i + 1;
		END LOOP;
 
		RETURN ret;
 
END;

$BODY$;

ALTER FUNCTION public.base36_decode(character varying)
    OWNER TO postgres;

base36_encode.sql

-- FUNCTION: public.base36_encode(bigint, integer)

-- DROP FUNCTION public.base36_encode(bigint, integer);

CREATE OR REPLACE FUNCTION public.base36_encode(
	digits bigint,
	min_width integer DEFAULT 0)
    RETURNS character varying
    LANGUAGE 'plpgsql'

    COST 100
    IMMUTABLE 
    ROWS 0
AS $BODY$

DECLARE
    chars char[]; 
    ret varchar; 
    val bigint; 
BEGIN
    chars := 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'];
    val := digits; 
    ret := ''; 
    IF val < 0 THEN 
        val := val * -1; 
    END IF; 
    WHILE val != 0 LOOP 
        ret := chars[(val % 36)+1] || ret; 
        val := val / 36; 
    END LOOP;

    IF min_width > 0 AND char_length(ret) < min_width THEN 
        ret := lpad(ret, min_width, '0'); 
    END IF;

    RETURN ret;
END;

$BODY$;

ALTER FUNCTION public.base36_encode(bigint, integer)
    OWNER TO postgres;

base36_timestamp.sql

-- FUNCTION: public.base36_timestamp()

-- DROP FUNCTION public.base36_timestamp();

CREATE OR REPLACE FUNCTION public.base36_timestamp(
	)
    RETURNS character varying
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 0
AS $BODY$

BEGIN
	RETURN base36_encode(CAST(extract(epoch from now() at time zone 'utc') * 1000 AS int8)); 
END;

$BODY$;

ALTER FUNCTION public.base36_timestamp()
    OWNER TO postgres;