How does MySQL store and display dates, and how do I control the display format?

Understanding MySQL Date Format

In MySQL, when a date field is created, it stores the date internally in a three-byte integer format: DD MM×32 YYYY×16×32. However, for display purposes, the date is shown in a human-readable format, such as '0000-00-00'.

Changing the Display Format

To display the date in a specific format such as 'd-m-Y', you can use the DATE_FORMAT() function. This function converts the raw date value into a VARCHAR with the desired format. For instance, the following query would display the date in the 'd-m-Y' format:

SELECT col1, col2, DATE_FORMAT(datecolumn, '%d-%m-%Y') AS datecolumn, more1...
FROM sometable
....

Important Considerations

It's important to remember that the stored date value is not affected by the display format. The datecolumn itself remains an integer value. When working with dates in a programming environment, it's crucial to handle the raw date values and use appropriate formatting functions from the programming language for display purposes.

Reference

For further information, refer to the MySQL Reference: 10.5. Data Type Storage Requirements, specifically the section on "Storage Requirements for Date and Time Types."

The above is the detailed content of How does MySQL store and display dates, and how do I control the display format?. For more information, please follow other related articles on the PHP Chinese website!