[FIXED] How to convert varchar(10) column to timestamp

Issue

I have an old Database table with varchar(10) column with timestamp data.

Now i am using laravel web app on that Database.

I need to convert varchar(10) column to timestamp with data.

Previously i have change the column Type using phpmyadmin (on a backup copy) but all the data were change to 0000000000.

How can i do this Properly without any data lost.

timestamp   varchar(10)     utf8_general_ci         No  None    

sampel data:(in varchar) 1246251403 1246251404 1248771150

Solution

Convert your string to a timestamp using from_unixtime(). It will run fine on a VARCHAR column.

Example :

create table mytable (oldts varchar(10), newts timestamp NULL);

insert into mytable (oldts,newts) values ('1246251403', NULL);
insert into mytable (oldts,newts) values ('1246251404', NULL);
insert into mytable (oldts,newts) values ('1246251404', NULL);

update mytable SET newts = from_unixtime(oldts);

select oldts, newts from mytable;

Results :

|      oldts |                newts |
|------------|----------------------|
| 1246251403 | 2009-06-29T04:56:43Z |
| 1246251404 | 2009-06-29T04:56:44Z |
| 1246251404 | 2009-06-29T04:56:44Z |

SQL Fiddle

Answered By - Thomas G