How to convert time in mysql characters

MySQL is a commonly used relational database management system. It supports data storage, retrieval, modification and other operations, and has good performance and reliability. In MySQL, character conversion time is a relatively common operation. It can easily convert string type time into the time format supported by MySQL to facilitate subsequent operations. This article will introduce the character conversion time operation in MySQL.

1. Date and time types in MySQL

In MySQL, date and time types include four types: DATE, TIME, DATETIME and TIMESTAMP. Among them, the DATE type represents date in the format of 'YYYY-MM-DD'; the TIME type represents time in the format of 'HH:MM:SS'; the DATETIME type represents date and time in the format of 'YYYY-MM-DD HH:MM'. :SS' The TIMESTAMP type also represents a date and time, but in the format 'YYYY-MM-DD HH:MM:SS' and is time zone dependent. When working with character conversion times, you need to understand the differences between these types and the formatting requirements.

2. Commonly used functions for character conversion time

In MySQL, commonly used character conversion time functions include STR_TO_DATE and DATE_FORMAT. Among them, the STR_TO_DATE function is used to convert the string type time into the time format supported by MySQL, and the DATE_FORMAT function is used to convert the time format supported by MySQL into other specified formats.

1.STR_TO_DATE function

The syntax of the STR_TO_DATE function is:

STR_TO_DATE(string, format)

where string represents the string type to be converted time, and format represents the target format. These two parameters need to be separated by commas. In the format, some placeholders need to be used to represent the year, month, day, hour, minute, second and other related information. The specific format is shown in the following table:

Placeholder	Meaning
%Y	Year, four digits
%m	Month, two digits
%d	Date, two digits
% H	Hours, 24-hour clock, two digits
%i	Minutes, two digits
%s	Seconds, two digits

For example, you want to change the time of string type '2019-11-08 12: 30:45' to convert to DATETIME type, you can use the following statement:

SELECT STR_TO_DATE('2019-11-08 12:30:45', '%Y-%m-%d %H:%i :%s') AS datetime;

After executing this statement, '2019-11-08 12:30:45' will be output, indicating that the string type time has been successfully converted to the DATETIME type.

2.DATE_FORMAT function

The syntax of the DATE_FORMAT function is:

DATE_FORMAT(date, format)

Among them, date represents the MySQL support to be converted Time format, and format represents the target format. Again, these two parameters need to be separated by a comma. In the format, some placeholders also need to be used to represent the year, month, day, hour, minute, second and other related information. The specific format is the same as the table above.

For example, if you want to convert the time format '2019-11-08 12:30:45' supported by MySQL to the format of 'November 8, 2019 12:30 minutes and 45 seconds', you can use the following Statement:

SELECT DATE_FORMAT('2019-11-08 12:30:45', '%Y year %m month %d day %H hour %i minute %s second') AS datetime;

After executing this statement, 'November 8, 2019 12:30:45' will be output, indicating that the time format supported by MySQL has been successfully converted to the specified format.

3. Summary

In MySQL, character conversion time is a very common operation, which can be achieved through functions such as STR_TO_DATE and DATE_FORMAT. When performing conversion operations, you need to understand the differences in date and time types and the placeholder requirements of the target format to ensure the correctness and effectiveness of the operation. At the same time, you also need to pay attention to the relationship between the time format supported by MySQL and the time zone to avoid unexpected errors.

The above is the detailed content of How to convert time in mysql characters. For more information, please follow other related articles on the PHP Chinese website!