Converting unix time to datetime with FROM_UNIXTIME() in MySQL

Table:

I'm wondering if there is a way to convert all my timestamps into native mysql 开发者_运维问答datetime (YYYY-MM-DD HH:MM:SS) using FROM_UNIXTIME(), but I'm falling short on examples I can pull from. MySQL is not my kung fu.

I'm assuming you want to replace the unix timestamp field with a native mysql datetime field. To do this, you'd need to add a new field to your table of the datetime type:

ALTER TABLE yourtable ADD newdatetimefield DATETIME NOT NULL;

Then do an update on your table:

update yourtable set newdatetimefield=from_unixtime(timestampfield);

Then you can drop the old unix timestamp field:

alter table yourtable drop timestampfield;

and rename the new field to the old name

alter table yourtable change newdatetimefield timestampfield datetime;