CODEWITHSUNDEEP
mysql
S16
S16

Reputation: 3003

Converting unix time to datetime with FROM_UNIXTIME() in MySQL

Table:

enter image description here

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.

Upvotes: 0

Views: 8032

Answers (1)

Marc B
Marc B

Reputation: 360572

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;

Upvotes: 6

Related Questions