How to Change Datetime Formats in MySQL

Problem:

You’d like to change the format of date and time data in a MySQL database.

Example:

Our database has a table named student_platform with data in the columns id, first_name, last_name, and registration_datetime.

For each student, let’s get their first name, last name, and registration date and time. However, we want to display the date and time in the following format: abbreviated weekday name, comma, year, month name, day of month, and the time in hours, minutes, and seconds. It should look like this:

Tue, 2019 February 17 11:18:55

Solution:

We’ll use the DATE_FORMAT() function. Here’s the query you’d write:

SELECT 
  first_name,
  last_name,
  DATE_FORMAT(registration_datetime, '%a, %Y %M %e %H:%i:%s')
    AS format_registration_datetime
FROM student_platform;

Here’s the result of the query:

Discussion:

In a MySQL database, the DATE_FORMAT() function allows you to display date and time data in a changed format.

This function takes two arguments. The first is the date/datetime to be reformatted; this can be a date/time/datetime/timestamp column or an expression returning a value in one of these data types. (In our example, we use the registration_datetime column of the datetime data type.)

The second argument is a string containing the desired date and time format. MySQL makes a number of specifiers available, like:

• %a – Abbreviated weekday name.
• %Y – Year, in 4-digits.
• %M – Full name of the month.
• %e – Day of the month (from 1 – 31).
• %H – Hour (from 00-23).
• %i – Minutes (from 00-59).
• %s – Seconds (from 00-59).

You can learn more about date and time specifiers here, in the official MySQL documentation.

For example, Lora Lorens registered on '2019-02-23 12:04:23'. Now, her registration date and time has the new format of 'Sat, 2019 February 23 12:04:23'.