How to convert date data to different formats in MySQL

MySQL is a commonly used relational database management system that can be used to store and manage various types of data. In MySQL, date and time data are stored in different formats, so there is often a need to convert date data from one format to another. This article will introduce how to convert date data into different formats in MySQL.

Date and time types in MySQL

In MySQL, date and time types can be represented by the following data types:

1. DATE type, representing date, format is YYYY-MM-DD, such as "2022-10-17".
2. TIME type represents time in the format of HH:MM:SS, such as "14:25:00".
3. DATETIME type, representing date and time, in the format YYYY-MM-DD HH:MM:SS, such as "2022-10-17 14:25:00".
4. TIMESTAMP type represents a date and time combination in the format of YYYY-MM-DD HH:MM:SS, such as "2022-10-17 14:25:00".

Convert date format

In MySQL, you can use the DATE_FORMAT() function to convert a date or time data into a different format. This function requires two parameters: the date or time data to be converted and the format of the conversion. The following are some commonly used formats:

1. %Y: represents the year, such as "2022".
2. %m: Indicates the month, such as "10".
3. %d: Indicates the date, such as "17".
4. %H: Indicates the hour, such as "14".
5. %i: Indicates minutes, such as "25".
6. %s: Indicates seconds, such as "00".

For example, to convert date data "2022-10-17" to the format of "20221017", you can use the following SQL statement:

SELECT DATE_FORMAT('2022-10-17','%Y%m%d');

The result will be "20221017".

Convert date and time format

If you want to convert date and time data to a different format, you can use the DATETIME_FORMAT() function. This function requires two parameters: the date or time data to be converted and the format of the conversion. The following are some commonly used formats:

1. %Y: represents the year, such as "2022".
2. %m: Indicates the month, such as "10".
3. %d: Indicates the date, such as "17".
4. %H: Indicates the hour, such as "14".
5. %i: Indicates minutes, such as "25".
6. %s: Indicates seconds, such as "00".

For example, to convert the date and time data "2022-10-17 14:25:00" to the format of "October 17, 2022 14:25:00", you can use The following SQL statement:

SELECT DATE_FORMAT('2022-10-17 14:25:00','%Y年%m月%d日 %H点%i分%s秒');

The result will be "October 17, 2022 14:25:00".

Convert UNIX timestamp

A UNIX timestamp is an integer value that represents a date and time in seconds. In MySQL, you can use the FROM_UNIXTIME() function to convert UNIX timestamps to date and time data. This function requires one parameter: the UNIX timestamp to be converted. The following is an example:

For example, to convert the UNIX timestamp "1644140700" (representing 14:38:20 on February 7, 2022) into date and time data, you can use the following SQL statement:

SELECT FROM_UNIXTIME(1644140700,'%Y-%m-%d %H:%i:%s');

The result will be "2022-02-07 14:38:20".

Summary

In MySQL, date and time data are stored in different formats. Date and time data can be converted into different formats using the DATE_FORMAT() and DATETIME_FORMAT() functions. Additionally, UNIX timestamps can be converted to date and time data using the FROM_UNIXTIME() function. Proficiency in these functions will help improve the data processing capabilities of the MySQL database.

The above is the detailed content of How to convert date data to different formats in MySQL. For more information, please follow other related articles on the PHP Chinese website!