2

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?

kouton
  • 1,941
  • 2
  • 19
  • 34
  • Two reasons; 1 for the learning aspect; and 2, because I think it'll save me 250+ MB in storage plus an equal amount in Index. And as I work with flaky internet connections, offline backups from the cloud will be slighly easier. – kouton May 03 '13 at 03:34

2 Answers2

5

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.
hjpotter92
  • 78,589
  • 36
  • 144
  • 183
  • That did the trick, thanks! Incidentally, does saving in the timestamp format save much space over 16mn records? – kouton May 03 '13 at 03:42
  • 1
    @Reddox http://stackoverflow.com/questions/2533767/mysql-whats-the-best-to-use-unix-timestamp-or-datetime and http://stackoverflow.com/questions/2948494/unix-timestamp-vs-datetime – hjpotter92 May 03 '13 at 03:43
  • 2nd query's pretty nifty. Thanks again! – kouton May 03 '13 at 03:47
  • If you're going to ever want milli or microseconds you should probably go "BIGINT" (64-bit, same as java long and c# long/int64) for your column type, plus 2038 is just around the corner. :) – William T. Mallard Mar 08 '15 at 22:08
0

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'));
efraimmgon
  • 1