Change Column Type Column Table

Question

I want to change the column type from TIME datatype to SMALL int in the number of seconds since midnight without changing the name.

I have a select statement that makes this change, but I am wondering if there is a way to directly change the data type without creating temporary fields.

 select ROUND(convert(begin_time, UNSIGNED)/100) % 100 + 60 * ROUND(convert(begin_time, UNSIGNED)/10000) from time_window; 

Answer 1

I do not think that you can change the scheme and data in one step. This does not mean that you cannot do this atomically - you can use a transaction to do this depending on your storage engine. But you have to do it step by step.

1) Change the name of the old column.

 ALTER TABLE time_window CHANGE COLUMN begin_time begin_time_old TIME; 

2) Add a new SMALL column with the old column name to save the new data.

 ALTER TABLE time_window ADD COLUMN begin_time SMALL; 

3) Insert data using your conversion.

 UPDATE time_window SET begin_time = ROUND(convert(begin_time_old, UNSIGNED)/100) % 100 + 60 * ROUND(convert(begin_time_old, UNSIGNED)/10000) 

4) Drop the old column.

 ALTER TABLE time_window DROP COLUMN begin_time_old