Search code examples
mysqlsqltimestampunix-timestamp

How can I change Unix timestamp data to MySQL timestamps in my database?

I am still pretty new to databases in general.

I created one and had my application get the current time as a Unix timestamp and stored in time_stamp int(11) NOT NULL.

Now I realize how dumb that was, but I don't want to lose my data.

Is there an UPDATE statement I can use in the MySQL command box to change all those INTs to MySQL TIMESTAMPs?

Alternatively, I have a dump of the database, so I guess I could also use PhpMyAdmin to redefine the column (which seems to set all my INTs to "00-00-0000 00:00:00") and then delete the table contents, global edit the SqlDump and import it.

But, if I do, how do I edit the dump? Here are a few lines of it ...

INSERT INTO `activity` (`badge_number`, `time_stamp`, `activity`, `bar_code`, `rfid_tag`)   
VALUES (0, 1350388291, 'login', '', ''), (0,1350388433, 'logout', '', ''),

So,

UPDATE ACTIVITY SET time_stamp = <what?> WHERE <what?>
Solution

  • Try either

    ALTER TABLE activity CHANGE time_stamp time_stamp VARCHAR(19);
UPDATE activity 
   SET time_stamp = FROM_UNIXTIME(time_stamp);
ALTER TABLE activity CHANGE time_stamp time_stamp TIMESTAMP;

    Here is SQLFiddle demo

    or

    ALTER TABLE activity ADD COLUMN time_stamp2 TIMESTAMP;
UPDATE activity 
   SET time_stamp2 = FROM_UNIXTIME(time_stamp);
ALTER TABLE activity DROP COLUMN time_stamp;
ALTER TABLE activity CHANGE time_stamp2 time_stamp TIMESTAMP;

    Here is SQLFiddle demo

    Note: Before you proceed with update make sure that you have a solid backup of your table/database. Just in case.