Convert existing string custom field to date

[cjwfuller]

Hello,

I have a customized mantis which has a custom field of type string.

I wanted to convert it to a date so I went into the Manage section of mantis and changed the string field to a date. It doesn't convert the strings to dates so all the records change into 1970:01:01 dates.

I tried storing the dates to a file using php, changing the field type in Manage section to type date and then running my script to correct the dates in the database. I then refresh a page which had an 1970 date and it didn't do anything. Interestingly, even when you get the incorrect 1970 dates, the dates in the database are still valid!

Anyone got any ideas how to do this?

Thanks in advance,

Chris.

[cjwfuller]

This: "the dates in the database are still valid" was meant to be this: "the dates in the database are still in date form"

[cjwfuller]

Ok this works now, I dumped the database, ran the scripts on the dumped database, re-dumped it and imported it over the real one (didn't have to worry about lost updates etc..). The code (comment out first the repairDates(), run it, then comment out the storeDates(): (Note, don't worry about the "if($exp[0] < 2008 or $exp[0] > 2020) {" - it's just because I have different string dates in the database :-S.

Code: Select all

function storeDates() {
		/* All custom field strings that are dates */
		$sql = "SELECT * FROM `mantis_custom_field_string_table` WHERE `field_id` = \"4\"";
		$query = mysql_query($sql);
		
		$f = fopen("extractedDates.txt", "a");
		while($res = mysql_fetch_array($query)) {
			if($res['value'] != "") {
				$theDate = $res['value'];
				$exp = explode('-', $theDate);

				if($exp[0] < 2008 or $exp[0] > 2020) {
					$theDate = $res['value'];
					$exp = explode('/', $theDate);
					$new = $exp[2]."-".$exp[1]."-".$exp[0];
				}
				else {
					$new = $res['value'];
				}
				print $new." ";
				print strtotime($new)."<br>";
				fwrite($f, $res['bug_id']." ".strtotime($new)."\n");
			}
			else {
				echo "ERRONEOUS DATE, COULD NOT PERFORM CONVERSION"."<br>";
			}
		}
		fclose($f);
	}
	
	function repairDates() {
	
		$foundFirst = false;
		$foundSecond = false;
		$f = fopen("extractedDates.txt", "r");
		
		while(!feof($f)) {
			$theData = fgets($f);
			
			$foundFirst = false;
			for($i = 0; $i < strlen($theData); $i++) {
				if(false == $foundFirst)
				{
					if($theData[$i] == " ") {
						$firstCol = substr($theData, 0, $i);
						$foundFirst = true; 	
						$remember = $i;
					}	
				}
			}
			$foundSecond = false;
			for($j = $remember; $j < strlen($theData); $j++) {
				if(true == $foundFirst and false == $foundSecond and $j != $remember) {
					$secondCol = substr($theData, $remember, strlen($theData));
					$foundSecond = true; 			
				}
			}
			$update = "UPDATE mantis_custom_field_string_table SET value = \"$secondCol\" WHERE bug_id = \"$firstCol\" AND field_id = \"4\" AND value != \"\"";
			mysql_query($update);
		}
		fclose($f);
		print "JOB DONE";
	}
	//repairDates();
	storeDates();