Re: Converting a "timestamp"
| From: | Jennifer | Date: | Mon, 20 Feb 2017 16:15:02 +0000 |
| Subject: | Re: Converting a "timestamp" | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-325982@lists.php.net to get a copy of this message | ||
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'),