How do you convert MySQL\'s internal date format to a human-readable string?

Understanding MySQL Date Format and Display Conversion

When creating a DATE field in MySQL, you may wonder why dates are stored internally as three-byte integers representing DD MM×32 YYYY×16×32. This format allows for efficient storage and rapid retrieval.

However, for display purposes, the stored date must be converted from its internal format to a human-readable string. To achieve this, MySQL provides the DATE_FORMAT() function.

Example:

Suppose you have a table with a DATE field named birthdate. To display the dates in the 'd-m-Y' format, use the following query:

SELECT birthdate, DATE_FORMAT(birthdate, '%d-%m-%Y') AS formatted_birthdate
FROM table_name;

Notice that the formatted date is stored in a new column named formatted_birthdate. This is because the original DATE field is stored as an integer, and cannot be modified directly.

Considerations:

• If you plan to use the retrieved dates in a programming language, it's recommended to extract the raw date value from the integer format, as string formatting may vary depending on the programming environment.
• Alternatively, you can define a custom MySQL function that converts the DATE field to the desired format and use it in your queries.

The above is the detailed content of How do you convert MySQL's internal date format to a human-readable string?. For more information, please follow other related articles on the PHP Chinese website!