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.
Here are the 5 steps needed:
- 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:
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:
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.
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