How to Convert a MySQL TIMESTAMP to a yyyy-mm-dd Date Format?

Converting TIMESTAMP to Date in MySQL Query

In MySQL, converting a timestamp to a date can be achieved using the FROM_UNIXTIME() function. To format the resulting date in a specific format (e.g., yyyy-mm-dd), the DATE_FORMAT() function can be utilized.

Original Query

$sql = requestSQL("SELECT user.email, 
                   info.name, 
                   FROM_UNIXTIME(user.registration),
                   info.news
                   FROM user, info 
                   WHERE user.id = info.id ", "export members");

Improved Query

To convert the user.registration timestamp to a date in the yyyy-mm-dd format, the following query can be used:

SELECT user.email, 
       info.name, 
       DATE_FORMAT(FROM_UNIXTIME(`user.registration`), '%Y-%m-%d') AS 'date_formatted',
       info.news
FROM user, info 
WHERE user.id = info.id

Explanation

• FROM_UNIXTIME(user.registration): Converts the timestamp in the user.registration field to a date and time.
• DATE_FORMAT(FROM_UNIXTIME(user.registration), '%Y-%m-%d'): Formats the date and time returned by FROM_UNIXTIME() into the yyyy-mm-dd format. The resulting date will be aliased as date_formatted.
• The remaining columns (user.email, info.name, and info.news) are selected as before.

After executing this query, the date_formatted column will contain the user.registration timestamp converted to the desired yyyy-mm-dd date format, making it suitable for inclusion in a text file or further processing.

The above is the detailed content of How to Convert a MySQL TIMESTAMP to a yyyy-mm-dd Date Format?. For more information, please follow other related articles on the PHP Chinese website!