Search code examples
mysqldatabasesqlyog

Converting string to timestamp format in MySQL

I have a column in my MySQL database which has timestamp data in string format. I want to convert it into timestamp format.

Below is the sample data:

Date
--------------------------------
Fri Dec 14 14:11:43 IST 2018
Fri Dec 14 14:13:20 IST 2018

I'm expecting the result to be in the following format:

14-12-2018 14:11:43
14-12-2018 14:13:20
Solution

  • By using the STR_TO_DATE() to convert the string into a DATETIME, then by using DATE_FORMAT() can change it to the expected date time format.

    The following query will return the expected output date time format:

    SELECT DATE_FORMAT(
         STR_TO_DATE('Fri Dec 14 14:11:43 IST 2018', '%a %b %d %T IST %Y'), 
       '%d-%m-%Y %H:%i:%s');

    Output:

    14-12-2018 14:11:43

    db<>fiddle demo

    Reference formats are available in this link