I'd like to convert unix timestamps from one column of an existing sql table to dates in another column of the same table. Something like this: take the values of column TIMESTAMP from every raw and convert it into a date and put it into column DATE.

I already figure out that the below expression converts the timestamps into dates:

SELECT FROM_UNIXTIME(TIMESTAMP) FROM MYTABLE

but I don't know how to plug this into a normal sql update expression.

Thanks for any advice!
W

CodePudding user response：

Use the expression in the SET clause of the UPDATE query.

UPDATE MYTABLE 
SET date = FROM_UNIXTIME(timestamp)