How to Get the Time From a String in MySQL

Problem

You want to extract the time from a string value in MySQL.

Example

You have a string value that looks like this: 'Wednesday, 10 February 2021, 12:30:20'.

You want to extract only the time part, '12:30:20'.

Solution

Here’s the query:

SELECT 
  DATE_FORMAT(
    STR_TO_DATE('Wednesday, 10 February 2021, 12:30:20', '%W, %d-%m-%Y, %T'), 
    '%T');

Discussion

To illustrate the process more clearly, we will explain this in two parts.

First, we have to convert the string to a date value. To do this, we use the STR_TO_DATE function. The query should look like this:

SELECT STR_TO_DATE('Wednesday, 10 February 2021, 12:30:20', '%W, %d-%m-%Y, %T');

The purpose of the STR_TO_DATE function is to convert a text string to a datetime format. The syntax for this function is as follows: STR_TO_DATE(string, format);

The first argument of this function is the string we want to convert. The second argument specifies the format of the string. In our case, we use the following parameters:

• %W: Weekday name from Sunday to Saturday.
• %d: Day of the month as a numeric value from 01 to 31.
• %m: Month as a numeric value from 01 to 12.
• %Y: Year as a 4-digit numeric value (yyyy).
• %T: Time in the 24-hour format (hh:mm:ss).

You can find more parameters in official documentation for MySQL.

The result of this query should look like this: 2021-02-10 12:30:20.

Now we have the value in the DATETIME data type. To extract only the time, we have to add the DATE_FORMAT function to the query above.

The general syntax of the DATE_FORMAT function is: DATE_FORMAT(date, format)

Then our query should look like this:

SELECT 
  DATE_FORMAT(
    STR_TO_DATE('Wednesday, 10 February 2021, 12:30:20', '%W, %d-%m-%Y, %T'),
    '%T');

In our example, the first argument of the DATE_FORMAT function is the date resulting from the STR_TO_DATE function. The second argument is the format, which works in the same way as it does in the STR_TO_DATE function. Since we only need the time here, we specify %T as the second argument.

The % character is mandatory before the format specifier characters. We must specify the format in exactly the same order, including all punctuation marks.

The query result we see below is the expected outcome: 12:30:20 .

By using a combination of these two functions, you can extract every possible combination of date and time from a text string.