Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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 !!!

share|improve this answer
1  
It works and took about 6 min to 3 million lines. Thanks! –  Felipe Micaroni Lalli Feb 8 '13 at 16:11
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.