Welcome! Log In Create A New Profile

Advanced

[PHP] Converting a "timestamp"

Posted by Jennifer 
Jennifer
[PHP] Converting a "timestamp"
February 18, 2017 09:30PM
I have a "timestamp" written to a text field in MySQL that I need to convert in every record. Currently it is formatted like 2017|02|16|04|58|42
and I want to convert it to this format 2017-02-16 @ 04:58:42

What’s the best way to do this? Do I need to read each record, convert it in PHP, and then insert it back into the table? Or is there a shorter way of doing this that doesn’t require both reading and writing each record?

Also, if it’s easier, is there a way to do this directly in MySQL?

I’m not really asking for code at this point, as I’m just trying to figure out how to get started.

Thank you,
Jenni
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Aziz Saleh
Re: [PHP] Converting a "timestamp"
February 18, 2017 09:50PM
On Sat, Feb 18, 2017 at 3:26 PM, Jennifer <[email protected]>
wrote:

> I have a "timestamp" written to a text field in MySQL that I need
> to convert in every record. Currently it is formatted like
> 2017|02|16|04|58|42
> and I want to convert it to this format 2017-02-16 @ 04:58:42
>
> What’s the best way to do this? Do I need to read each record,
> convert it in PHP, and then insert it back into the table? Or is there a
> shorter way of doing this that doesn’t require both reading and writing
> each record?
>
> Also, if it’s easier, is there a way to do this directly in MySQL?
>
> I’m not really asking for code at this point, as I’m just trying
> to figure out how to get started.
>
> Thank you,
> Jenni
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
Why is the code formatted like that? There are multiple mysql field formats
you can use (like datetime) which would make it tremendously easier for you
to search, modify, pickup, etc.. that format.

My suggestion to you if it is not too hard to go back to the insertion
point and fix it there, trust me it will make it easier in the long run.
The same things with other fields (ints, strings, etc..). It is always best
to have them right the first time around instead of getting stuck with bad
formats once your site grows.

To answer your question, you can achieve what you want with
substring/concat (assuming the # of digits is the same with all rows):

http://sqlfiddle.com/#!9/36332/12
Jennifer
Re: [PHP] Converting a "timestamp"
February 19, 2017 04:40AM
> On Feb 18, 2017, at 12:47 PM, Don Wieland <[email protected]> wrote:
>
> function convert_stamp($stamp) {
> $ta = explode(‘|’, $stamp);
> return $ta[‘0’].’-‘.$ta[‘1’].’-‘.$ta[‘2’].’ @ ‘.$ta[‘3’].’:‘.$ta[‘4’].’:‘.$ta[‘5’];
> }

Thanks for the function, Don. That did end up coming in handy, but my main question was about getting the data out of MySQL and then back in, which I couldn’t really solve. I could get the data into an array and massage it, but I couldn’t figure out how to get it back in. So I ended up just exporting the data from the table, manipulating the file in PHP, then importing back into the table. A round about way, but at least it’s done.

Jenni
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Lester Caine
Re: [PHP] Converting a "timestamp"
February 19, 2017 11:50AM
On 19/02/17 03:34, Jennifer wrote:
> Thanks for the function, Don. That did end up coming in handy, but my main question was about getting the data out of MySQL and then back in, which I couldn’t really solve. I could get the data into an array and massage it, but I couldn’t figure out how to get it back in. So I ended up just exporting the data from the table, manipulating the file in PHP, then importing back into the table. A round about way, but at least it’s done.

Jennifer ...
Your problem here is sorting out just what format IS used in the
database. If the field is a timestamp, it's stored as a number rather
than a string of characters. I think I am right in saying that a Mysql
timestamp is as a 32bit unix style seconds count, while a DATETIME is 64
bit with a layout of numbers something like you describe. The Mysql date
and time functions take care of any conversions, so it is your client
program that is showing the 2017|02|16|04|58|42 which I suspect is
'INTERNAL' format, while you want to use a different 'default' or use
the
https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
function to give you the correct format.

I don't use Mysql much. Firebird has always had a 64 bit timestamp which
is 32bits day and 32bits time and other databases use their own internal
format.

--
Lester Caine - G8HFL
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Narcis Garcia
Re: [PHP] Converting a "timestamp"
February 19, 2017 12:30PM
In SQL language you still can take Aziz example to perform this:
UPDATE MyTable SET MyDate = CONCAT(SUBSTRING(MyDate, 1, 4), '-',
SUBSTRING(MyDate, 6, 2), '-',...);

The result will be complete conversion of original rare format to your
customized format.


El 19/02/17 a les 04:34, Jennifer ha escrit:
>> On Feb 18, 2017, at 12:47 PM, Don Wieland <[email protected]> wrote:
>>
>> function convert_stamp($stamp) {
>> $ta = explode(‘|’, $stamp);
>> return $ta[‘0’].’-‘.$ta[‘1’].’-‘.$ta[‘2’].’ @ ‘.$ta[‘3’].’:‘.$ta[‘4’].’:‘.$ta[‘5’];
>> }
>
> Thanks for the function, Don. That did end up coming in handy, but my main question was about getting the data out of MySQL and then back in, which I couldn’t really solve. I could get the data into an array and massage it, but I couldn’t figure out how to get it back in. So I ended up just exporting the data from the table, manipulating the file in PHP, then importing back into the table. A round about way, but at least it’s done.
>
> Jenni
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Jennifer
Re: [PHP] Converting a "timestamp"
February 20, 2017 05:20PM
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'),



--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Sorry, only registered users may post in this forum.

Click here to login