Convert MySQL integer column into DateTime while preserving the data

Dominic PrawnStar Bauer
Feb 19 · 2 min read

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.

Image for post
Image for post
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

Image for post
Image for post
Shoutout to @lidyanada for the picture

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

Nerd For Tech

From Confusion to Clarification

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store