CODEWITHSUNDEEP
phpmysql
kouton
kouton

Reputation: 1961

Bulk convert and update datetime column values to UNIX timestamp?

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?

Upvotes: 2

Views: 2167

Answers (2)

efraimmgon
efraimmgon

Reputation: 1

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'));

Upvotes: 0

hjpotter92
hjpotter92

Reputation: 80639

As it'll be a one time change; you can proceed this way:

  1. 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`;

  2. Update table:

    UPDATE `nameOfTable`
SET `created` = UNIX_TIMESTAMP( `created_at` ),
    `updated` = UNIX_TIMESTAMP( `updated_at` );
  3. Remove the older columns and rename newer ones back to created_at and updated_at.

Alternative way:

  1. Set the DATETIME columns to VARCHAR field.

  2. Update using the query:

    UPDATE `nameOfTable`
SET `created_at` = UNIX_TIMESTAMP( `created_at` ),
    `updated_at` = UNIX_TIMESTAMP( `updated_at` );
  3. Change the columns to INT.

Upvotes: 5

Related Questions