How Can I Convert Unix Timestamps to Human-Readable Dates in MySQL?

Convert Unix Timestamps into Human-Readable Dates with MySQL

Often, timestamps are stored in a database in Unix timestamp format, a numerical representation of the number of seconds that have elapsed since January 1, 1970. While this representation is efficient for storage, it's not human-readable.

MySQL Solution

To transform Unix timestamps into a human-readable date format, MySQL provides the FROM_UNIXTIME() function. This function takes the Unix timestamp as its input and returns the corresponding date in various formats. The format can be specified using the optional % character followed by a date or time specifier, such as %Y-%m-%d %H:%i:%s for the full date and time with seconds.

Example Usage

To convert a Unix timestamp stored in the timestamp column of the your_table table into a human-readable date, use the following SQL query:

SELECT
  FROM_UNIXTIME(timestamp) 
FROM 
  your_table;

Additional Information

For more details and options related to the FROM_UNIXTIME() function, refer to the MySQL documentation: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_from-unixtime

The above is the detailed content of How Can I Convert Unix Timestamps to Human-Readable Dates in MySQL?. For more information, please follow other related articles on the PHP Chinese website!