Background
I have a MySQL db with around 16 million records. There are 2 columns created_at and updated_at which are presently in datetime format.
The Problem
I'd like to change this to UNIX Timestamp by converting all the values and updating the records with the new values.
I know how to do this with PHP in loops, but is there a way to perform this update by executing a single query in MySQL?
As it'll be a one time change; you can proceed this way:
Create new columns with INT datatypes and name them, say, created and updated.
ALTER TABLE `nameOfTable`
ADD COLUMN `created` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `created_at`,
ADD COLUMN `updated` INT UNSIGNED NOT NULL DEFAULT '0' AFTER `updated_at`;
Update table:
UPDATE `nameOfTable`
SET `created` = UNIX_TIMESTAMP( `created_at` ),
`updated` = UNIX_TIMESTAMP( `updated_at` );
created_at and updated_at.Alternative way:
DATETIME columns to VARCHAR field.Update using the query:
UPDATE `nameOfTable`
SET `created_at` = UNIX_TIMESTAMP( `created_at` ),
`updated_at` = UNIX_TIMESTAMP( `updated_at` );
INT.
hjpotter92's answer helped a lot.
But in my case it did not solve the problem right away, since the dates I had stored were in a format not accepted as an argument by UNIX_TIMESTAMP.
So I had to first convert it to an accepted format. After some research I got to the following query that made it:
UPDATE tableName set newFieldName = UNIX_TIMESTAMP(STR_TO_DATE(CAST(priorFieldName AS CHAR), '%m/%d/%y'));
