Ah, I see the issue here—you've got the timestamp conversion functions backwards! Let me break this down and fix it for you.

The error occurs because UNIX_TIMESTAMP() is meant to take a datetime value/string and convert it into a Unix timestamp (the numeric format your created column uses). But what you need is the opposite: turning that numeric Unix timestamp into a proper TIMESTAMP type. That's where FROM_UNIXTIME() comes in—it's the function that converts Unix timestamp numbers into readable datetime values.

Fix 1: Use a temporary column (safest for data verification) #

This approach lets you check converted data before replacing your original column, which is great for avoiding accidental data loss:

1. Add a new temporary TIMESTAMP column to your table:

ALTER TABLE share ADD created_temp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

2. Populate the temporary column by converting your existing Unix timestamps:

UPDATE share SET created_temp = FROM_UNIXTIME(created);

3. Double-check that all values in created_temp look correct (run SELECT created, created_temp FROM share LIMIT 10; to spot-test). Once verified, swap the columns:

-- Drop the original BigInt column
ALTER TABLE share DROP COLUMN created;
-- Rename the temp column to your original column name
ALTER TABLE share CHANGE created_temp created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Fix 2: Direct modification (faster, for trusted data) #

If you’re confident in your data and want to skip the temporary column, you can adjust the column type in a way that forces proper conversion. Note: This skips the verification step, so make sure you have a backup first:

-- First convert the column to DATETIME using the correct conversion logic
ALTER TABLE t MODIFY c DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
-- Then convert DATETIME to TIMESTAMP (optional, since TIMESTAMP is just a timezone-aware DATETIME in MySQL)
ALTER TABLE t MODIFY c TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

Pro Tip for Large Tables #

If your table has millions of rows, the UPDATE statement might lock the table for extended periods. For production environments, use batch updates (e.g., updating rows in chunks with LIMIT and a loop) to minimize downtime.

内容的提问来源于stack exchange，提问作者Lars Kakavandi-Nielsen