How to Convert MySQL Timestamps to Human-Readable Dates?

Converting Timestamp to Date in MySQL Queries

When working with timestamps in MySQL, there are often instances where you may need to convert them to a more human-readable date format. This is especially useful when exporting data to text files or displaying dates in applications.

One common method to achieve this conversion is by using the FROM_UNIXTIME() function. However, you mentioned in your question that you have already attempted this approach unsuccessfully. This is because FROM_UNIXTIME() only converts the timestamp to a Unix timestamp, which is still a numeric value.

To convert the timestamp to a proper date, you need to use the DATE_FORMAT() function along with FROM_UNIXTIME() as follows:

DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%Y-%m-%d') AS 'formatted_date'

This code snippet demonstrates the conversion of the timestamp in the user.registration column to a date in the format 'yyyy-mm-dd' and aliases the result as 'formatted_date'.

You can then incorporate this expression into your original query to achieve the desired output. Here's an example:

$sql = requestSQL("SELECT user.email, 
                   info.name,
                   DATE_FORMAT(FROM_UNIXTIME(user.registration), '%Y-%m-%d') AS 'formatted_date',
                   info.news
                   FROM user, info 
                   WHERE user.id = info.id", "export members");

Once you execute this modified query, you will get the formatted_date field in the text file in the desired 'yyyy-mm-dd' format.

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