How to Extract a YYYY-MM-DD Date from a MySQL Timestamp?

How to Extract Date from a Timestamp in MySQL

Converting timestamps to dates in MySQL allows you to manipulate and display date information in a user-friendly and structured manner. This process is particularly useful when dealing with timestamps stored in database tables.

One common scenario where date conversion is necessary is when exporting data from a MySQL database to a format that requires date values. This data could be used for various purposes, such as creating reports or importing into other systems.

In your specific case, you want to format the user.registration field from a MySQL table into a yyyy-mm-dd format. To achieve this, you can use the following SQL expression:

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

This expression combines multiple MySQL functions to convert the timestamp in user.registration to a date.

• FROM_UNIXTIME() converts the timestamp from Unix epoch (seconds since January 1, 1970) to a human-readable date and time.
• DATE_FORMAT() then formats the date portion of the result using the specified %Y-%m-%d format, where %Y represents the year with four digits, %m represents the month with two digits, and %d represents the day of the month with two digits.

By adding this expression to your query, you will create a new virtual column named 'date_formatted' that contains the date portion of the converted timestamps. You can then use this column in your export operation to obtain the desired date format.

The above is the detailed content of How to Extract a YYYY-MM-DD Date from a MySQL Timestamp?. For more information, please follow other related articles on the PHP Chinese website!