mysql date conversion

MySQL is a widely used relational database management system, and its date type has a variety of formats. When we perform data query or data import, sometimes we need to convert the date type from one format to another. In this article, we will discuss MySQL date type conversion methods.

MySQL date type

MySQL supports multiple date types, including:

1. DATE type: represents date, in the format of 'YYYY-MM-DD', Such as 2021-07-21.
2. TIME type: indicates time in the format of 'HH:MM:SS', such as 14:30:45.
3. DATETIME type: Represents date and time in the format of 'YYYY-MM-DD HH:MM:SS', such as 2021-07-21 14:30:45.
4. TIMESTAMP type: Similar to DATETIME, but can display fewer numbers, in the format 'YYYY-MM-DD HH:MM:SS'.

Date format conversion

In MySQL, we can use the DATE_FORMAT function to convert date types from one format to another.

DATE_FORMAT(date, format)

Among them, date is the date type to be converted, and format is the date format to be converted. The following are some common date formats:

%Y: four-digit year, such as 2021

%m: two-digit month, such as 07

%d : two-digit date, such as 21

%H: hour in 24-hour format, such as 14

%i: two-digit minute, such as 30

%s: Two-digit seconds, such as 45

For example, if we want to convert the date 2021-07-21 into the format of '21 July, 2021', we can use the following statement:

SELECT DATE_FORMAT('2021-07-21','%d %M, %Y');

The output result is: '21 July, 2021'

Similarly , we can also convert the time type from one format to another.

Time format conversion

In MySQL, we can use the TIME_FORMAT function to convert the time type from one format to another.

TIME_FORMAT(time, format)

Among them, time is the time type to be converted, and format is the time format to be converted. The following are some common time formats:

%h: the number of hours in 12-hour format, such as 02

%H: the number of hours in 24-hour format, such as 14

%i: Two-digit minutes, such as 30

%s: Two-digit seconds, such as 45

%p: AM or PM

For example, If we want to convert the time 14:30:45 into the format of '02:30:45 PM', we can use the following statement:

SELECT TIME_FORMAT('14:30:45','%h: %i:%s %p');

The output result is: '02:30:45 PM'

Date and time format conversion

Similarly, we also Date and time types can be converted from one format to another.

In MySQL, we can use a combination of the DATE_FORMAT and TIME_FORMAT functions to convert date and time types from one format to another.

For example, if we want to convert the date and time type '2021-07-21 14:30:45' into the format of 'Wednesday, 21 July 2021, 02:30:45 PM', we can use The following statements:

SELECT CONCAT( DATE_FORMAT('2021-07-21 14:30:45','%W, %d %M %Y, '), TIME_FORMAT('14:30:45' ,'%h:%i:%s %p') );

The output result is: 'Wednesday, 21 July 2021, 02:30:45 PM'

In the above statement , we used the DATE_FORMAT function to convert the date into the format of 'Wednesday, 21 July 2021, ', used the TIME_FORMAT function to convert the time into the format of '02:30:45 PM', and used the CONCAT function to convert the two strings connect them.

Conclusion

Date and time formats are very important in MySQL. By using DATE_FORMAT and TIME_FORMAT functions we can easily convert date and time types from one format to another. Understanding these functions is very helpful for data query and data import.

The above is the detailed content of mysql date conversion. For more information, please follow other related articles on the PHP Chinese website!