Converting Timestamps to Dates in MySQL Queries

When working with timestamps in MySQL queries, it can often be necessary to convert them into a more human-readable format, such as a date in 'yyyy-mm-dd' format. To achieve this, MySQL provides several functions that enable you to manipulate and format timestamps.

One method for converting timestamps to dates is to use the FROM_UNIXTIME() function, followed by the DATE_FORMAT() function. The FROM_UNIXTIME() function converts the timestamp into a Unix timestamp, which is the number of seconds since the epoch. The DATE_FORMAT() function then allows you to specify a format string to format the resulting timestamp.

In your case, you can use the following query to convert the user.registration field to a date in 'yyyy-mm-dd' format:

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

This query uses the FROM_UNIXTIME() and DATE_FORMAT() functions to convert the timestamp stored in the user.registration field into the desired format. The resulting date will be stored in the date_formatted column.

By incorporating this conversion into your query, you can easily extract and format timestamp data as recognizable dates, enabling you to conveniently work with date-based information in your application or context.