Detailed explanation of several methods to convert MySQL timestamp into readable time format

In MySQL, time is usually stored as a UNIX timestamp, which is the time in seconds since January 1, 1970. However, in actual applications, we sometimes need to convert these timestamps into a more readable format, such as year, month, day, hour, minute, second, etc. This article introduces several methods to convert MySQL timestamps into readable time formats.

1. Using the FROM_UNIXTIME() function

FROM_UNIXTIME() is a system function in MySQL that is used to convert UNIX timestamps to date and time format. The syntax of this function is as follows:

FROM_UNIXTIME(unix_timestamp[,format])

Among them, unix_timestamp is the UNIX timestamp, and format is an optional parameter used to specify the output time format.

For example, convert the timestamp to the format of year, month and day:

SELECT FROM_UNIXTIME(1562568000,'%Y-%m-%d');

This will output: 2019-07-08.

If you need to convert the timestamp into a more detailed format, you can use the following statement:

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

This will output: 2019-07-08 08:00:00.

2. Using the DATE_FORMAT() function

DATE_FORMAT() is another system function in MySQL that is used to convert the date and time format to a specified format. The syntax of this function is as follows:

DATE_FORMAT(date,format)

Among them, date is the date and time type data, and format is the converted format.

For example, convert the timestamp to the format of year, month and day:

SELECT DATE_FORMAT(FROM_UNIXTIME(1562568000),'%Y-%m-%d');

This will output: 2019-07-08.

If you need to convert the timestamp into a more detailed format, you can use the following statement:

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

This will output: 2019-07-08 08:00:00.

3. Using the TIMESTAMP() function

The TIMESTAMP() function is a system function in MySQL that is used to convert date and time type data into UNIX timestamps. The syntax of this function is as follows:

TIMESTAMP(date)

Among them, date is date and time type data.

For example, convert date time to timestamp format:

SELECT UNIX_TIMESTAMP('2019-07-08 08:00:00');

This will output: 1562568000.

4. Using the UNIX_TIMESTAMP() function

The UNIX_TIMESTAMP() function is a system function in MySQL, used to convert date and time type data into UNIX timestamps. The syntax of this function is as follows:

UNIX_TIMESTAMP(date)

Among them, date is date and time type data.

For example, convert date time to timestamp format:

SELECT UNIX_TIMESTAMP('2019-07-08 08:00:00');

This will output: 1562568000.

Summary

In MySQL, converting timestamps into readable time formats is a very common need. This article introduces several commonly used methods, including using the FROM_UNIXTIME() function, DATE_FORMAT() function, TIMESTAMP() function and UNIX_TIMESTAMP() function. Depending on the specific needs, choosing different methods can more conveniently achieve time format conversion.

The above is the detailed content of Detailed explanation of several methods to convert MySQL timestamp into readable time format. For more information, please follow other related articles on the PHP Chinese website!