CODEWITHSUNDEEP
mysqldate
Curtis Crewe
Curtis Crewe

Reputation: 4336

Change timestamp to date in mySQL

I'm just trying to change my database layout from using time to date("y-m-d h:i:s") would it be possible to do this using just an SQL query? or would I have to manually do one by one?

It's because I started inserting timestamps into the database, now I need to store date(), I don't want a simple bypass by just converting timestamp to date using PHP, I want to run an SQL to update all rows from timestamps to date.

something like:

UPDATE `my_table` SET `time` = 'new_date("y-m-d h:i:S") WHERE `time` = 'time()'

Upvotes: 0

Views: 195

Answers (1)

Cfreak
Cfreak

Reputation: 19319

You'll need to add a DATETIME type field to store it then you can use a the FROM_UNIXTIME() function in mysql to convert it. 

ALTER TABLE my_table ADD my_date_time DATETIME;
UPDATE my_table SET my_date_time = FROM_UNIXTIME(`time`);

documentation on FROM_UNIXTIME(): http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

You can then drop your old column named time. I would recommend that anyway because time is a column type in mysql.

ALTER TABLE my_table DROP `time`;

Upvotes: 2

Related Questions