mysql timestamp conversion

MySQL is a commonly used relational database management system that supports processing timestamps. A timestamp is a recorded method of representing time and date in most computer systems. Timestamps can be stored in a MySQL database and then queried, sorted, etc. However, when using timestamps, you sometimes encounter some conversion problems. This article will introduce the MySQL timestamp conversion method.

1. Comparison between UNIX timestamp and MySQL timestamp

Unix timestamp is a time representation in Unix, which represents the number of seconds since January 1, 1970. 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 can format a date into any format 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.

4. Summary

This article introduces the MySQL timestamp conversion method. If you want to convert a UNIX timestamp to a MySQL timestamp, use the FROM_UNIXTIME() function or the DATE_FORMAT() function. If you want to convert a MySQL timestamp to a UNIX timestamp, you can use the UNIX_TIMESTAMP() function. When using these functions, make sure to pass them the correct format string.

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