Convert all columns in rows from date to MySQL timestamp

Question

I have a database with 13,000 rows. Each row contains a “date” column, which is now in this format (example): 2012-09-01 17:53:28but I want them to be TIMESTAMP.

What query should I execute to update all columns named 'date' from date to timestamp for all rows?

thank

Answer 1

It is very easy to convert from one type to another using the ALTER TABLE command :

ALTER TABLE table_name CHANGE old_date new_timestamp TIMESTAMP

This process may take some time if you have a lot of data and a large number of indexes.

, TIMESTAMP, , DATETIME. .

Answer 2

, . : date, time, datetime timestamp. UNIX_TIMESTAMP, 1970-01-01, , .

.

DATETIME , , . MySQL DATETIME "--" : : . : "1000-01-01 00:00:00" '9999-12-31 23:59:59'.

TIMESTAMP , , . TIMESTAMP '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

date do:

ALTER TABLE yourTable MODIFY COLUMN yourDateColumn TIMESTAMP;

Answer 3

SELECT UNIX_TIMESTAMP('2012-09-01 17:53:28');

Answer 4

You can simply use the UNIX_TIMESTAMP () function to return values ​​as a timestamp when executing a query. For instance:

mysqli_query("select UNIX_TIMESTAMP(date) as date from table");