I have a table with hex timestamps as ids like this:
Id Name
015b2a63ec53 Alice
015b2aa24993 Bob

and I want to create a view which renders it like this:
Creation Date Name
2017-04-01 16:40:43 Alice
2017-04-01 17:48:50 Bob

Your approach is the only one I found which worked to convert the id. However, I wasn't able to create a working view for the whole table as my SQL skills are a little limited 😅

Maybe you can help me @ChiChou?

Utilizing ChatGPT-4 alongside this ChiChou snippet, I achieved a result akin to the following:

WITH 
    hexchars(key, value) AS (
        VALUES
            ('0', 0),('1', 1),('2', 2),('3', 3),('4', 4),('5', 5),('6', 6),('7', 7),('8', 8),('9', 9),
            ('A', 10),('B', 11),('C', 12),('D', 13),('E', 14),('F', 15),
            ('a', 10),('b', 11),('c', 12),('d', 13),('e', 14),('f', 15)
    ),
    -- Get all UUID from table
    all_uuids AS (
        SELECT uuid, replace(substr(uuid, 0, 14), '-', '') as hex_timestamp FROM podcasts_tts
    )
    -- Processing each UUID
SELECT
    uu.uuid,
    (
        WITH RECURSIVE
            -- Converting the part of timestamp hex to decimal
            unhex(str, val, weight) AS (
                SELECT uu.hex_timestamp, 0, 1
                    UNION ALL
                SELECT 
                    substr(str, 1, length(str) - 1),
                    val + (select value from hexchars where key = substr(str, length(str), 1)) * weight,
                    weight * 16
                FROM unhex WHERE length(str) > 0
            )
        SELECT val FROM unhex ORDER BY weight DESC LIMIT 1
    ) AS decimal_timestamp
FROM
    all_uuids uu;

It's a monster, but it works.

In my case, I have a column UUID v7 with has a timestamp inside, so I need to extract it with replace(substr(uuid, 0, 14), '-', '')

It's as easy as hexstr->>'$' using the JSON features introduced by version 3.38.0 (2022-02-22).

See detailed description on Stack Overflow