3

I am trying to convert datetime columns of my database to timestamp without data loss. I followed this answer: Bulk convert and update datetime column values to UNIX timestamp?

but when I want to:

UPDATE `members` SET `new_join_date` = UNIX_TIMESTAMP(`join_date`), `new_last_visit` = UNIX_TIMESTAMP(`last_visit`);

I get this error:

#1292 - Incorrect datetime value: '1376833381' for column 'new_join_date' at row 1

full convertion queries:

ALTER TABLE `members`
ADD COLUMN `new_join_date` TIMESTAMP NULL AFTER `join_date`,
ADD COLUMN `new_last_visit` TIMESTAMP NULL AFTER `last_visit`;

UPDATE `members`
SET `new_join_date` = UNIX_TIMESTAMP(`join_date`),
`new_last_visit` = UNIX_TIMESTAMP(`last_visit`);

ALTER TABLE `members` DROP `join_date`;

ALTER TABLE `members` DROP `last_visit`;

ALTER TABLE `members` CHANGE `new_join_date` `join_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `members` CHANGE `new_last_visit` `last_visit` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
Community
  • 1
  • 1
exim
  • 606
  • 1
  • 8
  • 24

1 Answers1

0

MySQL TIMESTAMP and UNIX_TIMESTAMP are not the same type!

You declared new_join_date and new_last_visit to be of type TIMESTAMP and then try to set it to a different type, hence the error.

To work around it you should declare these columns as INT(11).

Nir Alfasi
  • 53,191
  • 11
  • 86
  • 129