CODEWITHSUNDEEP
mysql
Frederik Spang
Frederik Spang

Reputation: 3454

Change a column from varchar to timestamp, keeping values

I have a column (varchar), that holds values like "hh:mm", in 24h format. Parsed correctly by STR_TO_DATE(column, '%H:%i').

How do I alter the table - Changing the values to a TIMESTAMP - And keep the values?

Upvotes: 0

Views: 1492

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

If the format of '%H:%i' was correctly maintained, then you can alter column definition as below: 

alter table table_name modify column column_name time;

Demo: 

mysql> create table so.tm( t varchar(10) );
mysql> desc tm;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| t     | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

mysql> insert into tm values ( '09:45' ), ( '18:24' ), ( 'ravin' );
Query OK, 3 rows affected (0.08 sec)

mysql> select t, str_to_date( t, '%H:%i' ) from tm;
+-------+---------------------------+
| t     | str_to_date( t, '%H:%i' ) |
+-------+---------------------------+
| 09:45 | 09:45:00                  |
| 18:24 | 18:24:00                  |
| ravin | NULL                      |
+-------+---------------------------+

In case of the field containing data in no parseable time data, say 'ravin'', then the alter command would have thrown the following error. 

mysql> alter table tm modify column t time;
ERROR 1292 (22007): Incorrect time value: 'ravin' for column 't' at row 3

mysql> select t, str_to_date( t, '%H:%i' ) from tm;
+-------+---------------------------+
| t     | str_to_date( t, '%H:%i' ) |
+-------+---------------------------+
| 09:45 | 09:45:00                  |
| 18:24 | 18:24:00                  |
| ravin | NULL                      |
+-------+---------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> delete from tm where t is null;
Query OK, 0 rows affected (0.06 sec)

mysql> alter table tm modify column t time;
Query OK, 2 rows affected (1.05 sec)

mysql> desc tm; select t from tm;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| t     | time | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+

+----------+
| t        |
+----------+
| 09:45:00 |
| 18:24:00 |
+----------+

Upvotes: 1

Related Questions