Converting mysql column from INT to TIMESTAMP

This way doesn't require temp field to store timestamp type values, so should be more downtime friendly when altering on huge tables, and it's also avilable with DATE(TIME) type fields (see below).

For convert timestamp or datetime to int see: Mysql: Convert column from timestamp to int and perform conversion for each update

First we have to cast timestamp type to numeric datetime without punctuation (like 202102060020302), since numeric full form is too long for int to store, should alter field to bigint type (if you need datetime type this would be TEXT type):

ALTER TABLE `table` CHANGE `field` `field` BIGINT NOT NULL;

then cast timestamp to numeric datetime (if you need datetime type, CAST() should be removed):

UPDATE `table` SET `field` = CAST(FROM_UNIXTIME(`field`) AS UNSIGNED);

finally we could change field type to timestamp and mysql will do the rest about converting numeric datetime (if you need datetime type just use DATETIME):

ALTER TABLE `table` CHANGE `field` `field` TIMESTAMP NOT NULL;

ref https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-conversion.html

> mysql> SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;
> +-----------+-------------+--------------+
> | CURTIME() | CURTIME()+0 | CURTIME(3)+0 |
> +-----------+-------------+--------------+
> | 09:28:00  |       92800 |    92800.887 |
> +-----------+-------------+--------------+
> mysql> SELECT NOW(), NOW()+0, NOW(3)+0;
> +---------------------+----------------+--------------------+
> | NOW()               | NOW()+0        | NOW(3)+0           |
> +---------------------+----------------+--------------------+
> | 2012-08-15 09:28:00 | 20120815092800 | 20120815092800.889 |
> +---------------------+----------------+--------------------+

You're nearly there, use FROM_UNIXTIME() instead of directly copying the value.

-- creating new column of TIMESTAMP type
ALTER TABLE `pm`
  ADD COLUMN `date_sent2` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP();

-- Use FROM_UNIXTIME() to convert from the INT timestamp to a proper datetime type
-- assigning value from old INT column to it, in hope that it will be recognized as timestamp
UPDATE `pm` SET `date_sent2` = FROM_UNIXTIME(`date_sent`);

-- dropping the old INT column
ALTER TABLE `pm` DROP COLUMN `date_sent`;

-- changing the name of the column
ALTER TABLE `pm` CHANGE `date_sent2` `date_sent` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP();