mysql format conversion

MySQL format conversion

MySQL is one of the most commonly used relational database management systems at present. It has excellent performance and reliability in data storage and management. In the process of using MySQL, you often encounter situations where you need to format data, such as converting timestamps to date format, converting strings to numbers, etc. The following will introduce common format conversion functions and their usage in MySQL.

1. DATE_FORMAT()

The DATE_FORMAT() function is used to format the date into a specified string format. Its general syntax is:

DATE_FORMAT(date, format)

Among them, date represents a date column or an expression representing a date, and format represents the date format to be output. For example, to format a timestamp field into date format, the statement is as follows:

SELECT DATE_FORMAT(timestamp, '%Y-%m-%d') AS date FROM table;

The above statement formats the timestamp field into the date format of YYYY-MM-DD and names it as the date field.

2. CAST()

The CAST() function is used to convert one data type to another data type. Its general syntax is:

CAST(expression AS type)

Among them, expression represents the expression to be converted, and type represents the converted data type. For example, to convert a string type to an integer type, the statement is as follows:

SELECT CAST(str AS SIGNED) AS num FROM table;

The above statement converts the value of the str field to an integer type and names it num field.

3. CONVERT()

The CONVERT() function can also be used to convert one data type to another. Its general syntax is:

CONVERT(expression, type)

Among them, expression represents the expression to be converted, and type represents the converted data type. For example, to convert a timestamp type to a date type, the statement is as follows:

SELECT CONVERT(FROM_UNIXTIME(timestamp), DATE) AS date FROM table;

The above statement converts the value of the timestamp field to a date type and names it as a date field.

4. UNIX_TIMESTAMP()

The UNIX_TIMESTAMP() function is used to convert date and time format to timestamp format. Its general syntax is:

UNIX_TIMESTAMP(date)

Among them, date represents the date or date time column. For example, to convert the current date and time into timestamp format, the statement is as follows:

SELECT UNIX_TIMESTAMP(NOW()) AS timestamp;

The above statement converts the current date and time into timestamp format and names it as the timestamp field.

5. FROM_UNIXTIME()

FROM_UNIXTIME() function is used to convert the timestamp format to date or datetime format. Its general syntax is:

FROM_UNIXTIME(timestamp, format)

Among them, timestamp represents the timestamp column, and format represents the converted date or date and time format. For example, to convert a timestamp type to a date and time type, the statement is as follows:

SELECT FROM_UNIXTIME(timestamp, '%Y-%m-%d %H:%i:%s') AS datetime FROM table;

The above statement converts the value of the timestamp field into the date and time format of YYYY-MM-DD HH:MM:SS, and Name the field datetime.

6. STR_TO_DATE()

The STR_TO_DATE() function is used to convert string format to date format. Its general syntax is:

STR_TO_DATE(str, format)

Among them, str represents the string column, and format represents the date format to be converted. For example, to convert a string type to a date type, the statement is as follows:

SELECT STR_TO_DATE(str, '%Y-%m-%d') AS date FROM table;

The above statement converts the value of the str field into the date format of YYYY-MM-DD and names it as the date field.

The above are common format conversion functions and their usage in MySQL. Mastering these functions can improve the efficiency and accuracy of data processing. However, when using these functions, you need to pay attention to the matching of data type and format to avoid unnecessary errors.

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