Convert MySQL integer column into DateTime while preserving the data

I recently ran into an issue where I was getting integer value timestamp data from an API which I simply saved into an integer column of a table.

The client I was working with then decided that they needed to the integer column of timestamps to be converted into human readable DateTime. I needed to do this conversion while preserving the data.

After Googling around for quite some time I finally found an answer that worked which I think is worth sharing.

Shoutout to @ikukevk for the picture

Here are the 5 steps needed:

1. Create a temp column in the same mysql table with the DateTime type

2. In mysql table run one of the following queries depending on your use case:

a) You have a 10 digit timestamp value e.g. 1613759058

Run the following:

UPDATE table 
SET 
temp_column = FROM_UNIXTIME(integer_timestamp_column)

This takes the integer_timestamp_column values and converts them into a DateTime using FROM_UNIXTIME. It then copies the value into the temp_column.

b) If timestamp is longer then 10 digits (meaning it accounts for millie seconds or more) you need to convert it. I had a 13 digit timestamp so to convert I did the following:

FLOOR(integer_timestamp/1000)

First it divides by 1000 — leaving 3 decimals. Then FLOOR is applied which converts the number to an integer (which is what we need in the next step) by rounding down.

Then run:

UPDATE table 
SET 
temp_column = FROM_UNIXTIME(FLOOR(integer_timestamp_column/1000))

Again copying the converted values from the integer_timestamp_column into the temp_column.

3. Delete integer_timestamp_column

4. Rename temp_column to integer_timestamp_column

Shoutout to @lidyanada for the picture

Voila you have converted your table integer column into a DateTime column while preserving the data!