2

I have stored all the date into my database as varchar(10), now I want to converse them into TIMESTAMP.

When I run sql

ALTER TABLE  `demo3` CHANGE  `date`  `date` TIMESTAMP NOT NULL

it alert:

#1292 - Incorrect datetime value: '1320534000' for column 'date' at row 1

BTW, All my date formart are 10 digital number.

ajreal
  • 46,720
  • 11
  • 89
  • 119
fish man
  • 2,666
  • 21
  • 54
  • 94

3 Answers3

3

You should first change the timestamp to datetime and then can change the type of column.

ALTER TABLE `demo3` MODIFY COLUMN `date` varchar(25);

UPDATE `demo3` SET `date`= FROM_UNIXTIME(`date`);

ALTER TABLE  `demo3` CHANGE  `date`  `date` TIMESTAMP NOT NULL
Shakti Singh
  • 84,385
  • 21
  • 134
  • 153
  • That's cool - I thought you needed an intermediate column to transform into. I'll try this later - thanks! – dash Dec 02 '11 at 12:18
1

You need to add a new column with the type you want, then update the table, converting the string to number for each row in an update statement.

So Add your new timestamp column with a default of NULL

Then run something similar to:

UPDATE demo3 SET new_timestamp = CONVERT(date, signed)

(You may need to try converting from unix timestamp and back again - see http://kitt.hodsden.org/mysql/converting_to_mysqls_timestamp_from_int11)

Which should push the integers into the timestamp column.

Then get rid of the original date column and rename the timestamp column.

dash
  • 89,546
  • 4
  • 51
  • 71
0

First you have to convert that unix timestamp format with the FROM_UNIXTIME function.

golimar
  • 2,419
  • 1
  • 22
  • 33