Mysql – What happens when changing column type DATETIME to TIMESTAMP in MySQL

datetimeMySQLtimestamp

I want to convert a column data type from DATETIME to TIMESTAMP in a table with more than one million of lines.

Is it possible? Is it slow? Will I lose the old information or MySQL can convert this automatically? Can I just do an alter table or may I do something different?

Best Answer

Here is a test script:

use test
drop table if exists felipe_table;
create table felipe_table
(
    id int not null auto_increment,
    dt datetime not null,
    name varchar(25),
    primary key (id)
);
insert into felipe_table (dt,name) values
(NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'),
(NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'),
(NOW() + INTERVAL FLOOR(RAND()*1000) DAY,'dominique'),
(NOW() + INTERVAL FLOOR(RAND()*10000) DAY,'diamond');
select * from felipe_table;
SHOW CREATE TABLE felipe_table\G
ALTER TABLE felipe_table MODIFY dt TIMESTAMP;
select * from felipe_table;
SHOW CREATE TABLE felipe_table\G

Here is its execution (using MySQL 5.5.12-log for Windows):

mysql> use test
Database changed
mysql> drop table if exists felipe_table;
Query OK, 0 rows affected (0.03 sec)

mysql> create table felipe_table
    -> (
    ->     id int not null auto_increment,
    ->     dt datetime not null,
    ->     name varchar(25),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into felipe_table (dt,name) values
    -> (NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'),
    -> (NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'),
    -> (NOW() + INTERVAL FLOOR(RAND()*1000) DAY,'dominique'),
    -> (NOW() + INTERVAL FLOOR(RAND()*10000) DAY,'diamond');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from felipe_table;
+----+---------------------+-----------+
| id | dt                  | name      |
+----+---------------------+-----------+
|  1 | 2013-02-16 09:01:21 | rolando   |
|  2 | 2013-02-09 09:01:21 | pamela    |
|  3 | 2014-09-07 09:01:21 | dominique |
|  4 | 2036-03-07 09:01:21 | diamond   |
+----+---------------------+-----------+
4 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE felipe_table\G
*************************** 1. row ***************************
       Table: felipe_table
Create Table: CREATE TABLE `felipe_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` datetime NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE felipe_table MODIFY dt TIMESTAMP;
Query OK, 4 rows affected (0.31 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from felipe_table;
+----+---------------------+-----------+
| id | dt                  | name      |
+----+---------------------+-----------+
|  1 | 2013-02-16 09:01:21 | rolando   |
|  2 | 2013-02-09 09:01:21 | pamela    |
|  3 | 2014-09-07 09:01:21 | dominique |
|  4 | 2036-03-07 09:01:21 | diamond   |
+----+---------------------+-----------+
4 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE felipe_table\G
*************************** 1. row ***************************
       Table: felipe_table
Create Table: CREATE TABLE `felipe_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

Looks like it works. If you do not trust this small sample, then make a copy of the table and perform this with the copy.

Give it a Try !!!

Related Question