Re: [PHP] Converting a "timestamp"

	Thank you for the extra replies.  I may have been overthinking the conversion process, but now I have two ways of doing what I want.  A friend of mine found a complete MySQL solution which is really elegant.  It does the conversion “in place” _and_ in the blink of an eye!

UPDATE `search_log` set `date_time` = CONCAT(
  SUBSTRING_INDEX(`date_time`, '|', 1), '-',
  SUBSTRING_INDEX(SUBSTRING_INDEX(`date_time`, '|', -5), '|', 1), '-',
  SUBSTRING_INDEX(SUBSTRING_INDEX(`date_time`, '|', -4), '|', 1), ' ',
  SUBSTRING_INDEX(SUBSTRING_INDEX(`date_time`, '|', -3), '|', 1), ':',
  SUBSTRING_INDEX(SUBSTRING_INDEX(`date_time`, '|', -2), '|', 1), ':',
  SUBSTRING_INDEX(SUBSTRING_INDEX(`date_time`, '|', -1), '|', 1)
);

	I also wrote a PHP solution that I mentioned yesterday (see below) which takes an SQL export file and manipulates the timestamp which I then import back into the table.

	And, yes, I did convert the source code that generates the timestamp ;) and changed the field to DATETIME.

Thanks again,
Jenni



<?php

$handle1 = fopen('search_log.sql', 'r');
$handle2 = fopen('search_log_2.sql', 'w');

while (($line = fgets($handle1)) !== FALSE) {
if (substr($line, -2, 1) == ';') { 
     $line_ending = '';  }
else { $line_ending = ",\n"; }

if (substr($line, 0, 1) == '(') {  // If the line starts with a '('
$cells = explode(',', $line);
$new_date = convert_timestamp($cells[5]);
$new_lines = "$cells[0],$cells[1],$cells[2],$cells[3],$cells[4],$new_date,$cells[6],$cells[7],$cells[8],$cells[9]$line_ending"; 
} else {
$new_lines = $line;
}

fwrite($handle2, $new_lines);
}
fclose($handle1);
fclose($handle2);


function convert_timestamp($ts) {
if (strpos($ts, '|') !== FALSE) {
$ta = explode('|', $ts);
return $ta['0'].'-'.$ta['1'].'-'.$ta['2'].' '.$ta['3'].':'.$ta['4'].':'.$ta['5'];
   } else {
return $ts;
   }
}
?>

Sample data:

('1040', 'frame', 34, NULL, '147.154.235.12', '2016|08|01|12|16|49', NULL, 'frame', ‘ ', 'both'),
('1041', 'wines', 22, NULL, ‘176.111.171.20', '2016|08|01|12|45|16', NULL, 'wines', ‘ ', 'both’),
('1042', 'cover', 35, NULL, '172.156.24.132', '2016|03|17|18|08|56', NULL, 'cover', ‘ ', 'both'),