Create new column in mysql to convert unix time into readable date and time

Question

I am trying to create a new column in my table to convert a unix time column into human readable date and time.

I have found a few functions but am having trouble with the syntax as I do not normally use mysql. In this case it is required though.

Also, the unix time column I currently have is in milliseconds, could you please assist with this also.

Thanks

farbiondriven · Accepted Answer · 2016-12-02 09:01:37Z

1

Is the unix time column in the same table ? You can add a datetime column to the table, then run an update to translate the old records:

update foo_table set datetimecolumn=from_unixtime(unixcolumn);

Then you can add a trigger to the table to auto translate every time an insert is performed:

CREATE TRIGGER `toDateTime` BEFORE INSERT ON `foo_table` FOR EACH ROW  set new.datetimecolumn=from_unixtime(new.unixcolumn);

UPDATE:

as it is in milliseconds, you can use this

from_unixtime(CAST(unixcolumn/1000 as BIGINT))

answered Dec 2, 2016 at 2:56

farbiondriven

2,4782 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1