Convert VARCHAR timestamp to TIMESTAMP?

Question

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?

Solution 1

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;

Solution 2

Use str_to_date with formatting:

select unix_timestamp(str_to_date("17:16:28 Sep 13, 2011 PDT","%T %b %d, %Y PDT"));

If the day part (e.g.: 13) is not represented as 01, 02..etc. but 1, 2, 3 when it's only one digit, change the %d to %e

Be careful because the timezone will not be recognized, it's only a string literal for the formatting! If you have different timezones for different records you should use the convert_tz() function to get the proper timestamp.

Comments

[Mark]

I've noticed that everybody always comes up with exactly the same example, containing "17:16:28 Sep 13, 2011 PDT". This isn't what I'm looking for. I don't want to convert a string. I want to convert a SQL field. Also, got any examples for convert_tz()?

[Mark]

See my comment to the other answer.

[Omesh]

Updated answer with query. Also see stackoverflow.com/questions/2523286/mysql-convert-tz

[Andrew]

I suppose it's because the example is taken from your question :) Also @Omesh already answered the other part on how to convert the field. More specifically to convert the varchar value into another field which is a good way IMO.

[Mark]

Many thanks, Omesh. This worked perfectly. I found out I had only two time zones in my database and for each time zone I executed update tablename set newdate=STR_TO_DATE(olddate, '%H:%i:%s %b %d, %Y PDT') where olddate like '%PDT%' (the second time changing PDT into PST) and got what I wanted.

[Mark]

Aha... you're right about the date itself, but what I meant is that wherever I look on stackoverflow.com people tend to use strings instead of field references, which really surprises me, since MySQL is usually about how to read and process data from fields rather than strings. Additionally, I don't understand why you added the unix_timestamp function. This is realy unnecesary.

[Andrew]

You're right. MySQL is really about the fields rather than the strings. I suppose it's easier to show a sample in one line with a string than explain what kind of data you have in a table and use it's field(s) in an example query. You're right about the unix_timestamp, it's not necessary. You were asking for a timestamp so it made sense in my head but no so much when I'm reading it back :)