How to convert time in MySQL

In MySQL, time is often stored in the table in the form of strings, but this is not convenient for time calculation and comparison. Therefore, it is necessary to convert the string type time into a time type to facilitate our related operations. This article will introduce how to convert time in MySQL.

In MySQL, time can be represented by DATETIME, TIMESTAMP, DATE, TIME and other types. The time these types store in MySQL is UTC time.

To convert string type time to time type, you can use the STR_TO_DATE() function provided by MySQL. 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. Identifier case: identifiers in the format string (such as 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. Delimiter: The delimiter used in the format string must be consistent with the delimiter used in the actual string. For example, if "-" is used as a date separator in the actual string, then "-" must also be used as a separator in the format string.
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), an error will be returned; if strict is not used mode, NULL will be 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. The UNIX timestamp represents the number of seconds from 00:00:00 on January 1, 1970 to the current time. It is an integer. 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.

Since UNIX timestamps and time types in MySQL are both UTC times, 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".

Summary:

In MySQL, time is often stored in tables in the form of strings. In order to facilitate time calculation and comparison, the string type time needs to be converted into a time type for direct calculation and comparison. You can use the STR_TO_DATE() and UNIX_TIMESTAMP() functions provided by MySQL to convert the string type time to a time type or UNIX timestamp, or you can use the DATE_FORMAT() function to convert the time type to a string type. When using conversion functions, you need to pay attention to the format string writing specifications and the use of strict mode.

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