Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Previously, on my table (~81000 rows), I have had date and time fields set as VARCHAR instead of date/time. All those dates were of the type MM/DD/YY. I changed the type from VARCHAR to DATE and TIME respectively and most of my dates, which had DD >= 13 to 0000-00-00.

Now, I am left with a backup I took 3 months ago (~77400 rows) and then the newer ones. Is there any way that I can restore the older dates?

The way I see it, I'll have to try updating the dates using the older .SQL file for those 77400 rows and after that will have to write a script to check and increment date with each passing 24 hour span in the TIME column.

I didn't have a backup taken after 12th April and didn't consider taking one while altering the database. :(

EDIT

mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i486) using readline 6.1

CREATE TABLE `latest` (
  `msg` VARCHAR(280) NOT NULL,
  `id` VARCHAR(20) NOT NULL,
  `ctg` VARCHAR(10) NOT NULL,
  `date` VARCHAR(50) NOT NULL COMMENT 'date_format( %m/%d/%y )',
  `time` VARCHAR(50) NOT NULL COMMENT 'date_format( %H:%i:%s )',
  `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`fid`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=80685

I changed the table code from that to this:

CREATE TABLE `latest` (
  `msg` VARCHAR(280) NOT NULL,
  `id` VARCHAR(20) NOT NULL,
  `ctg` VARCHAR(10) NOT NULL,
  `date` DATE NOT NULL COMMENT 'date_format( %m/%d/%y )',
  `time` TIME NOT NULL COMMENT 'date_format( %H:%i:%s )',
  `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`fid`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=80685
share|improve this question

migrated from stackoverflow.com Jul 10 '12 at 15:38

This question came from our site for professional and enthusiast programmers.

    
If this is a question about what you can practically do at this point, it's a better fit for one of the other SE sites. Also, backups. –  Jon Jul 7 '12 at 13:26
    
What's the type of the table in question? Version of MySQL also won't hurt. –  raina77ow Jul 7 '12 at 13:52
    
@raina77ow added them on edit. –  hjpotter92 Jul 7 '12 at 14:04
add comment

2 Answers 2

up vote 2 down vote accepted

Well, usually I use the following approach: instead of altering a live column type (if it's not something really trivial), I add another column of a new type, then set all the values in it (more on it below), then delete the old column after doing all that (and checking that all works ok), using its name as a name for a new column.

Perhaps this approach can still be used. To convert old values use something like this:

UPDATE my_table 
   SET datetime_column = STR_TO_DATE(varchar_column, '%d-%m-%Y')

Please take note that actual format literal might be different in your case; consult the manual and test it for some exact value to be sure.

share|improve this answer
add comment

Its not possible to restore the older dates. If you change the type of a field in SQL, the server (mySQL?) tries to find the right value by itself. if this fails, it will insert the default value. The older values are lost.

The Solution would be to write a script (PHP?) which will parse all your data and then save it correctly in the database.

The strtotime and date function of PHP could help you a lot. http://de.php.net/manual/de/function.strtotime.php & http://php.net/manual/de/function.date.php

For Example:

date("Y-m-d H:i:s", strtotime(YOUR VCHAR))
share|improve this answer
    
If only I had PHP, such a problem wouldn't have arrived. I only can use luasql. Thanks for the suggestion. :) –  hjpotter92 Jul 7 '12 at 14:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.