Generate a ULID with a specific TIMESTAMPTZ embedded in it

encode_ulid_from_timestamp.sql

SELECT
    overlay(
        overlay(
            gen_random_ulid()::STRING
        PLACING
            overlay(
                '0' -- caution: prepending '0' is only appropriate until June of 2527
                ||
                to_hex(
                    (
                        extract(epoch FROM the_timestamp)
                        *
                        1000
                    )::INT8
                )
            PLACING
                '-'
            FROM
                9
            FOR
                0
            )
        FROM
            1
        FOR
            13
        )
    PLACING
        lpad(
            to_hex(
                (
                    extract(epoch FROM the_timestamp)
                    *
                    1000000
                    %
                    1000
                )::INT8
            ),
            3,
            '0'
        )
    FROM
        15
    FOR
        3
    )::UUID
;