how to convert multiple mysql column string to date

Question

please i have a modified_by column in mysql table with string like this "40,1280825613|40,1280825617". the "40" is the id of the user that made changes to the record and "1280825613" is the date string. The | separates different periods of change.

How do i change the entire column to a readable date without affecting the user id. either mysql or php solution is welcome. Thank you so much.

Jason McCreary · Accepted Answer · 2011-05-19 22:40:20Z

1

I'd recommend a PHP script. You'll need to make two columns modified_by to retain the user id and modified for the timestamp. If there are multiple modified_by for each record you'll probably want to make a separate table, i.e. revisions. This would be the best way to store the data relationship. I'd also recommend not storing formatted data. You should already see why that's not a good idea. The database is the raw data, use PHP to format it.

Once you have that setup, just:

Select the data from the old table.
Loop over the records
explode() the column on |
Loop over the array
explode() the element on ,
Insert into new columns/table

Forgive me, but I'd rather teach you how to fish.

answered May 19, 2011 at 22:40

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ayodeji Over a year ago

wow, thats super fast. thank you for the suggestion. will try that pending other suggestion. thank you once again

Collectives™ on Stack Overflow

how to convert multiple mysql column string to date

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related