0

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

wanderlusted
  • 187
  • 13
  • what type is `TIMESTAMP`? what timezone does its data reflect, and is that the same as the timezone you want to use to determine the date? Showing your schema, some sample data and expected results for that sample data is always helpful – ysth Oct 15 '21 at 17:11
  • from_unixtime does not convert into a date – ysth Oct 15 '21 at 17:14

1 Answers1

1

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

UPDATE MYTABLE 
SET date = FROM_UNIXTIME(timestamp)
Barmar
  • 741,623
  • 53
  • 500
  • 612