Convert VARCHAR timestamp to TIMESTAMP?

datetime, mysql, time, timestamp

Solution

You can do this by using STR_TO_DATE function in MySQL, try this:

SELECT STR_TO_DATE("17:16:28 Sep 13, 2011 PDT", '%H:%i:%s %b %d, %Y PDT');

EDIT:

SELECT STR_TO_DATE(field_name, '%H:%i:%s %b %d, %Y PDT') AS new_time
FROM table_name;

Problem

I have a time stamp of the form "17:16:28 Sep 13, 2011 PDT" in a MySQL database. The type of the field in the database is VARCHAR. I would like to convert this VARCHAR to a field of type TIMESTAMP in MySQL. I tried a few solutions suggested elsewhere on this site, such as using a string_to_time function, but these solutions all start from a different type of timestamp. How do I convert the VARCHAR timestamp mentioned above to a TIMESTAMP recognised by MySQL, in such a way that I can sort my data by date?

Original source

Related problems