How convert field from timestamp to datetime

Question

I have a legacy database where there is table that has fields: start_date TIMESTAMP, expiration_date DATETIME.

It cause issues with some queries if database is used on database with different timezones. So to avoid them, I want to convert start_date to DATETIME. How could I safely convert from TIMESTAMP to DATETIME? Won't I lose any data if I change type of start_date from TIMESTAMP to DATETIME? How will mysql convert dates TIMESTAMP to DATETIME?

Answer 1

This is how I would do it:

1) Create a new datetime column

ALTER TABLE mytable ADD COLUMN mydatetime DATETIME;

2) Update this column using the timestamp values

UPDATE mytable SET mydatetime=FROM_UNIXTIME(UNIX_TIMESTAMP(mytimestamp));

3) After a few integrity checks, delete the timestamp column

ALTER TABLE mytable DROP COLUMN mytimestamp;

This should be safe enough for you, as you can keep the original timestamp column as long as you want.

Here is a sqlfiddle example.

Answer 2

In MySQL you can simply change the datatype from TIMESTAMP to DATETIME with no loss of data:

ALTER TABLE table_name MODIFY start_date DATETIME;