How to perform time conversion in MySQL

MySQL supports different types such as DATETIME, TIMESTAMP, DATE, and TIME to represent time. The time these types store in MySQL is UTC time.

Use the STR_TO_DATE() function in MySQL to convert a time string into a time type. The syntax of this function is as follows:

STR_TO_DATE(str, format)

Among them, str represents the string to be converted, and format represents the format of the date and time in the string. For example:

SELECT STR_TO_DATE('2019-08-14 21:12:23', '%Y-%m-%d %H:%i:%s');

This SQL statement will return a DATETIME type time, indicating 21:12:23 on August 14, 2019.

When using the STR_TO_DATE() function, you need to pay attention to the following points:

1. In the format string, the identifier (for example, Y, m, d, H , i, s, etc.) are different in their case. Uppercase means strict matching, lowercase means loose matching. For example, %m represents the numeric month, which can accept formats such as 01 and 1; and %M represents the English month, which requires exact matching.

2. The delimiter used in the format string must be the same as the delimiter used in the actual string. If "-" is used as the date separator in the actual string, the format string must also use "-" as the separator.

3. Strict mode: When the format string cannot match the actual string, if strict mode is used (such as setting sql_mode to STRICT_ALL_TABLES in the MySQL configuration file), it will be returned Error; if strict mode is not used, NULL is returned.

In addition to the STR_TO_DATE() function, you can also use the UNIX_TIMESTAMP() function to convert a string type time to a UNIX timestamp. A UNIX timestamp is an integer that represents the number of seconds that have elapsed since 00:00:00 on January 1, 1970, which is the current time. For example:

SELECT UNIX_TIMESTAMP('2019-08-14 21:12:23');

This SQL statement will return an integer representing the UNIX timestamp of 21:12:23 on August 14, 2019.

Because the time types in UNIX timestamp and MySQL are based on UTC time, they can be compared and calculated directly. For example, you can use UNIX_TIMESTAMP() to convert the time type to a UNIX timestamp, and then subtract them to get the time difference between the two times. For example:

SELECT UNIX_TIMESTAMP('2019-08-14 21:12:23') - UNIX_TIMESTAMP('2019-08-14 21:12:21');

This SQL statement will return an integer, representing the time difference between 21:12:23 on August 14, 2019 and 21:12:21 on August 14, 2019. That is 2 seconds.

In addition to converting string type time to time type, you can also use the DATE_FORMAT() function to convert time type to string type. The syntax of this function is as follows:

DATE_FORMAT(date, format)

Among them, date is the time to be converted, and format is the returned string format. For example:

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

This SQL statement will return the string representation of the current time in the format of "YYYY-MM-DD HH:MI:SS".

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