Re: Converting a "timestamp"

On Sat, Feb 18, 2017 at 3:26 PM, Jennifer <jennifer@superiorshelving.com> 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