Mawg Mawg - 1 month ago 10
MySQL Question

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 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
INT
s to MySql
TIMESTAMP
s?




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?>


Thanks in advance for any help.

Answer

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.

Comments