TIMESTAMP to DATETIME transition?

Question

How can I convert a column with TIMESTAMP to DATETIME and retain the dates? I'm using PHPMyAdmin.

Also timestamp had the option to autofill, is there a way to do so in DATETIME? Or should I insert it each time with PHP?

thanks

Answer 1

ALTER TABLE tablename MODIFY COLUMN columnname DATETIME;

(test on a test table first...)

No way to set the default to the current time save for triggers:

  DELIMITER $
  CREATE TRIGGER tablename_before_insert BEFORE INSERT ON tablename
  FOR EACH ROW 
  BEGIN
    IF NEW.columnname IS NULL THEN
      SET NEW.columnname = NOW();
    END IF;
    IF NEW.datum = '0000-00-00 00:00:00' THEN
      SET NEW.columnname = NOW();
    END IF;
  END$
  DELIMITER ;

Answer 2

If this query

ALTER TABLE table CHANGE `time` `time` datetime

will lose dates you can create new column then assign old values then delete old column and rename new one

ALTER TABLE table ADD `datetime` datetime AFTER `time`;
UPDATE table set datetime=time;
ALTER TABLE table DROP datetime;
ALTER TABLE CHANGE `datetime` `time` datetime

Read this:
This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE.

Answer 3

Use FROM_UNIXTIME() to convert from a unix timestamp into a regular datetime value.

A unix timestamp has no native type in mysql - it's simply an integer. timestamp fields will auto-populate, but only the first one in any given table. More details on that here.