How to use FROM_UNIXTIME function in MySQL to convert timestamp to date format

How to use the FROM_UNIXTIME function in MySQL to convert timestamps to date format

Timestamp (Timestamp) is a way to represent time. It is the total number of times calculated from a fixed point in time to the present. Number of seconds. In MySQL, we often need to convert timestamps into date format to perform some operations, such as the time range of statistical data, etc. At this time, you can use MySQL's FROM_UNIXTIME function to complete this conversion.

FROM_UNIXTIME function is to convert a timestamp parameter into a string in date format. Its syntax is:

FROM_UNIXTIME(unix_timestamp [,format])

Among them, unix_timestamp represents the timestamp to be converted, and format represents the date format to be output. The format parameter is optional, if not provided, the default output format is 'YYYY-MM-DD HH:MM:SS'.

Let’s look at some examples of using the FROM_UNIXTIME function:

Example 1: Convert a timestamp to a date string in the default format

SELECT FROM_UNIXTIME(1617685321) AS `Date`;

Output results:

+---------------------+
| Date                |
+---------------------+
| 2021-04-06 16:35:21 |
+---------------------+

Example 2: Convert timestamp to date string in specified format

SELECT FROM_UNIXTIME(1617685321, '%Y年%m月%d日 %H时%i分%s秒') AS `Date`;

Output result:

+-----------------------------+
| Date                        |
+-----------------------------+
| 2021年04月06日 16时35分21秒 |
+-----------------------------+

In example 2, we used '%Y year %m month %d day %H hour %i minute %s second' is used as the format parameter, which means to output the year, month, date, hour, minute and second.

In addition to the above two examples, the FROM_UNIXTIME function can also be used with other MySQL functions to achieve more complex time processing tasks.

Example 3: Use the FROM_UNIXTIME function and the DATE_FORMAT function to output the timestamp in the specified format

SELECT DATE_FORMAT(FROM_UNIXTIME(1617685321), '%Y-%m-%d') AS `Date`;

Output result:

+------------+
| Date       |
+------------+
| 2021-04-06 |
+------------+

In Example 3, we first use the FROM_UNIXTIME function to The timestamp is converted to date time format, and then the date time format is converted to the specified date format using the DATE_FORMAT function.

Through the above example, we can see that MySQL's FROM_UNIXTIME function is very convenient and powerful when converting timestamps to date format. Whether it's simple date conversion or complex time processing, it can meet our needs. If you encounter timestamp conversion problems when using MySQL for time processing, you might as well try the FROM_UNIXTIME function, I believe it will help you solve the problem easily.

The above is the detailed content of How to use FROM_UNIXTIME function in MySQL to convert timestamp to date format. For more information, please follow other related articles on the PHP Chinese website!