How to convert a unix time to GMT+8 human readable date without FROM_UNIXTIME() function?

Question

Since mysql Generated Column can't use FROM_UNIXTIME() as generate sql, how can I convert a unix time to GMT+8 human readable date? My failed example:

    ALTER TABLE just_a_table
    ADD COLUMN created_date
    CHAR(5) 
    GENERATED ALWAYS 
        AS (FROM_UNIXTIME(created_at, "%m/%d")) 
        STORED;

Answer 1

Since mysql Generated Column can't use FROM_UNIXTIME() as generate sql

???

FROM_UNIXTIME() is deterministic function. It can be used in generated column expression easily.

CREATE TABLE just_a_table (created_at BIGINT);
INSERT INTO just_a_table VALUES 
(UNIX_TIMESTAMP(NOW() - INTERVAL 1 HOUR)),
(UNIX_TIMESTAMP(NOW() - INTERVAL 2 DAY));

ALTER TABLE just_a_table
ADD COLUMN created_date CHAR(5) 
    GENERATED ALWAYS AS (FROM_UNIXTIME(created_at, "%m/%d")) STORED;

SELECT * FROM just_a_table

created_at | created_date
---------: | :-----------
1619167553 | 04/23       
1618998353 | 04/21       

db<>fiddle here