Find solution
How to convert VARCHAR to TIMESTAMP in MySQL?

mysql how to convert varchar(10) to TIMESTAMP?

  • 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.

  • Answer:

    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

fish man at Stack Overflow Visit the source

Was this solution helpful to you?

Other answers

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

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

golimar

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.