1

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;
DaveICS
  • 37
  • 2
  • 6

1 Answers1

0

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

Akina
  • 39,301
  • 5
  • 14
  • 25
  • I think is mysql version issue, my production env version did not allow me to do so, the error msg: `Function or expression 'from_unixtime()' cannot be used in the GENERATED ALWAYS` – DaveICS Apr 23 '21 at 10:43
  • @DaveICS Can you at least now tell us what version of MySQL server you have? – Akina Apr 23 '21 at 10:45
  • it's `10.4.17-MariaDB` – DaveICS Apr 23 '21 at 11:53
  • @DaveICS https://mariadb.com/kb/en/generated-columns/#expression-support *Most built-in functions are supported in expressions for generated columns. However, some built-in functions can't be supported for technical reasons.* You're just out of luck. Think, maybe VIRTUAL column is safe for you? https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=20c6595282731f934da0c03bb7819fac PS. Remember - MariaDB is NOT MySQL!!! Yes, it is highly compatible, but no more. – Akina Apr 23 '21 at 12:28