Change a MySQL Column datatype from text to timestamp?

To change a MySQL column datatype from text to timestamp, you need to use ALTER command.

The syntax is as follows

ALTER TABLE yourTableName MODIFY COLUMN yourColumnName TIMESTAMP;

To understand the above syntax, let us create a table.

The query to create a table is as follows

mysql> create table textTotimestampdemo
   - > (
   - > Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   - > Source text
   - > );
Query OK, 0 rows affected (0.44 sec)

Here is the description of table using DESC command.

The syntax is as follows

DESC yourTableName;

The query is as follows

mysql> desc textTotimestampdemo;

The following is the output

+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| Id     | int(11) | NO   | PRI | NULL    | auto_increment |
| Source | text    | YES  |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
2 rows in set (0.04 sec)

Now change the column text to timestamp with the help of the following query

mysql> alter table textTotimestampdemo modify column Source TIMESTAMP;
Query OK, 0 rows affected (1.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now check the description of the table once again.

The query is as follows

mysql> desc textTotimestampdemo;

The following is the output

+--------+-----------+------+-----+---------+----------------+
| Field  | Type      | Null | Key | Default | Extra          |
+--------+-----------+------+-----+---------+----------------+
| Id     | int(11)   | NO   | PRI | NULL    | auto_increment |
| Source | timestamp | YES  |     | NULL    |                |
+--------+-----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Look at the column Source above, the datatype has been changed from text to timestamp.

Chandu yadav

Published on 19-Mar-2019 11:47:33

• Related Questions & Answers
• For timestamp, which datatype is used in MySQL?
• How to convert from Unix timestamp to MySQL timestamp value?
• Change the file extension in the text column in MySQL?
• Change the column name from StudentName to FirstName in MySQL?
• How to change the datatype of a column in an existing table using JDBC API?
• MySQL query to select date from timestamp?
• Change the column name from a MySQL table with Student record?
• How to change MySQL column definition?
• Convert MySQL timestamp to UNIX Timestamp?
• Combine date and time column into a timestamp in MySQL?
• Change a MySQL column to have NOT NULL constraint
• How to select date from timestamp in MySQL?
• Update a column of text with MySQL REPLACE()
• How to get day name from timestamp in MySQL?
• Get first date from timestamp in MySQL group by another column with duplicate value