Within the table I've got a area (Varchar) known as CallTime.

It offers the occasions for example 8:00 PM, 8:40 PM, 10:00 AM

I wish to alter the area type to Some time and update time format.. just how can that be achieved?


I'd do that within the following steps:

  • Produce a Time type column named CallTimeNew
  • Write a script that can take the values from CallTime, convert it to time using mktime() and also the buddies, and write it to CallTimeNew
  • Take away the CallTime column and relabel the CallTimeNew column to CallTime
UPDATE table_name SET CallTimeNew = DATE_FORMAT(STR_TO_DATE(CallTime,'%l:%i %p'), '%H:%i:%s');

or simply alter the column type as other user recommended.

In MySQL I possibly could just alter the area type from varchar to time, also it instantly transformed the format from 1:30 PM to 01:30:00.

Or else you can write a little PHP script to loop through all occasions, format all of them with date and strtotime, after which place them in a proper format before you decide to switch area types.

ALTER TABLE tbl_title CHANGE CallTime CallTime TIME