Converting BigInt to timestamp in MySQL

Question

I have a table, in this table I have a column which currently stores a value of type BigInt, I want to alter this column to contain 'timestamp' values instead.

How can I achieve this. I have tried:

ALTER TABLE t
MODIFY c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

For which I get:

#1292 - Incorrect datetime value: '1524120113' for column 'created_temp' at row 1

And

ALTER TABLE share ADD created_temp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
UPDATE share SET created_temp = UNIX_TIMESTAMP(created);

For which I get: #1292 - Incorrect datetime value: '1524120113'

The content of created is created using UNIX_TIMESTAMP()

Answer 1

For instance, say that you have the timestamp 1376750476349 and you want to convert it into date time. You could simply do the following if your timestamp is in seconds.

select FROM_UNIXTIME(timestamp_value);

however, and this is a recurrent problem for many users of the above function, if your timestamp is say in milliseconds, you will have to perform the required conversions to seconds` before using the function. So, in the case where the timestamp is in milliseconds you could do the following to get the datetime:

select FROM_UNIXTIME(timestamp_value/1000);

Hope this helps. Enjoy!

source

Answer 2

You got the first error because the DBMS cannot convert the column on the fly. The second error comes from the fact that UNIX_TIMESTAMP() expects a DATE, TIME or TIMESTAMP string instead of integer so you should instead use FROM_UNIXTIME().

UNIX_TIMESTAMP() documentation has an important note on the difference between the two functions:

If you use UNIX_TIMESTAMP() and FROM_UNIXTIME() to convert between TIMESTAMP values and Unix timestamp values, the conversion is lossy because the mapping is not one-to-one in both directions. For example, due to conventions for local time zone changes, it is possible for two UNIX_TIMESTAMP() to map two TIMESTAMP values to the same Unix timestamp value. FROM_UNIXTIME() will map that value back to only one of the original TIMESTAMP values.

--

Here's the steps you need to do the task (it can take a long time to complete because all rows will have to be rewritten!):

1. Add a new column to your database:

   ALTER TABLE mytable ADD new_column TIMESTAMP NULL; 
   -- PS: You might want to use a default value instead of NULL here.
   

2. Convert the old values and fill the new column with them:

   UPDATE mytable SET new_column = FROM_UNIXTIME(old_column) WHERE old_column IS NOT NULL;
   

3. Delete the old column:

   ALTER TABLE mytable DROP COLUMN old_column;
   

4. Rename the new column to the old name:

   ALTER TABLE mytable CHANGE new_column old_column;