Use MySQL's DATE_FORMAT function to customize the display format of date and time

Use MySQL's DATE_FORMAT function to customize the display format of date and time

Date and time are very important data types in the database. MySQL provides the DATE_FORMAT function to customize the display format of date and time. By using this function, we can flexibly control the output format of date and time to meet different needs.

The syntax for using the DATE_FORMAT function is as follows:

DATE_FORMAT(date, format)

Among them, the date parameter is the date or time value to be formatted, and the format parameter is a string specifying the output format.

Let’s demonstrate some commonly used custom date and time formats:

1. Year-Month-Day

   SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') as formatted_date;

   Output result example: 2023- 07-15

2. Year/Month/Day

   SELECT DATE_FORMAT(NOW(), '%Y/%m/%d') as formatted_date;

   Output result example: 2023/07/15

3. Year- Month-day hour: minute: second

   SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') as formatted_datetime;

   Output result example: 2023-07-15 12:34:56

4. ##Month abbreviation

   SELECT DATE_FORMAT(NOW(), '%b') as month_abbreviation;

   Example of output result: Jul

5. Full name of month

   SELECT DATE_FORMAT(NOW(), '%M') as month_full_name;

   Example of output result: July

6. Day of the week

   SELECT DATE_FORMAT(NOW(), '%W') as weekday;

   Output result example: Saturday

7. Output the difference between date and time

   SELECT DATE_FORMAT(TIMEDIFF('2023-07-15 12:00:00', NOW()), '%H:%i:%s') as time_diff;

   Output result example: 23:25:04

Through the above examples, we can see the power of the DATE_FORMAT function. It can not only meet basic date and time format requirements, but can also freely splice or modify format strings according to needs to output more personalized date and time formats.

It should be noted that the DATE_FORMAT function can only be used for columns or values ​​of date and time types. If you want to format other types of data, you need to convert it to date or time type first.

In addition, MySQL also provides other date and time related functions, such as DATE_ADD, DATE_SUB, TIMESTAMPDIFF, etc., which can be used for date and time calculation and comparison. In practical applications, we can combine these functions with the DATE_FORMAT function to process date and time data more flexibly to meet the needs of different business scenarios.

To sum up, the display format of date and time can be easily customized using MySQL's DATE_FORMAT function. Proper use of this function can make our database applications more flexible and readable, and improve development efficiency and user experience.

The above is the detailed content of Use MySQL's DATE_FORMAT function to customize the display format of date and time. For more information, please follow other related articles on the PHP Chinese website!