How to convert mysql timestamp

1. Comparison of UNIX timestamps and MySQL timestamps

Unix timestamp represents the number of seconds that have passed since January 1, 1970, and is a type of Unix timestamp Time representation method. In many Unix applications, timestamps are often represented as integers. For example, in PHP and Java, use the time() function to obtain the current UNIX timestamp.

MySQL supports complex timestamp formats, such as YEAR, MONTH, DAY, etc. These timestamps can be used in combination with date and time. MySQL supports timestamps in UNIX timestamp format, which is the same format as timestamps in Unix.

2. Convert UNIX timestamp to MySQL timestamp

Conversion between UNIX timestamp and MySQL timestamp is a common problem. In the MySQL database, the format of the timestamp is "YYYY-MM-DD HH:MI:SS", while in Unix the format of the timestamp is an integer in seconds. Let's take a look at how to convert UNIX timestamps to MySQL timestamps.

Method 1: Use FROM_UNIXTIME() function

MySQL provides a function called FROM_UNIXTIME(), which can convert UNIX timestamps into MySQL timestamps. This function accepts an integer parameter, representing the number of seconds since "1970-01-01 00:00:00" (UTC time).

For example, if we want to convert the Unix timestamp 1587211731 to a MySQL timestamp, we can use the following statement:

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

The result will be:

2020-04-18 20:35:31

If we want to convert the current time To convert to a MySQL timestamp, you can use the following statement:

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

This statement will get the current time and convert it to a MySQL timestamp.

Method 2: Use the DATE_FORMAT() function

Another method is to use the DATE_FORMAT() function. This function has the ability to convert date formats to various formats supported by MySQL, including timestamps.

The following is an example of implementing this method:

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

The result is the same as the above result:

2020-04-18 20:35:31

3. MySQL timestamp to UNIX timestamp

MySQL Timestamps can be converted to UNIX timestamp format using the UNIX_TIMESTAMP() function. This function accepts a date or time parameter and returns the number of seconds since "1970-01-01 00:00:00" (UTC time).

For example, if we want to convert the MySQL timestamp 2020-04-18 20:35:31 to a UNIX timestamp, we can use the following statement:

SELECT UNIX_TIMESTAMP('2020-04-18 20:35:31');

The result will be:

1587211731

If we want to convert the current time to a UNIX timestamp, we can use the following statement:

SELECT UNIX_TIMESTAMP(NOW());

This statement will get the current time and convert it to a UNIX timestamp.

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