Menu
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?
297
To convert a date string to a timestamp: Pass the date string to the Date() constructor. Call the getTime() method on the Date object. The getTime method returns the number of milliseconds since the Unix Epoch.
We can convert a datetime object into a timestamp using the timestamp() method. If the datetime object is UTC aware, then this method will create a UTC timestamp. If the object is naive, we can assign the UTC value to the tzinfo parameter of the datetime object and then call the timestamp() method.
You can, however, cast a TIMESTAMP to a DATE and then cast the DATE to a CHAR of less than 24 characters. For example: SELECT CAST (CAST (timestamp_col AS DATE) AS CHAR(10)) FROM table1; CHAR data type into a DATE TIME or TIMESTAMP data type.
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');
SELECT STR_TO_DATE(field_name, '%H:%i:%s %b %d, %Y PDT') AS new_time
FROM table_name;
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.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
