Mysql date functions: detailed description of date and time format conversion functions

In PHP website development, the date and time fields in Mysql database design are essential because the date format output by Mysql date functions is not compatible with the date format between PHP date functions, in PHP website development, the date and time fields in Mysql database design are essential because the date format output by Mysql date functions is not compatible with the date format between PHP date functions, this requires using the Mysql or PHP date conversion function to convert the date format based on the actual situation of the website. From the perspective of convenient development, it is a good habit to use the UNIX timestamp format for date calculation or storage for a large number of date computing, the UNIX timestamp is helpful for converting the date format between PHP and Msyq. next I will introduce some common Mysql date functions to facilitate date conversion in PHP development.

　　Mysql date format function DATE_FORMAT

Similar to the date function of the PHP date function, the date format initialization function DATE_FORMAT is also available in the Mysql database, which is mainly used to convert and output the date time of the Mysql database in the date format required by the user. Only the date format code is different. In Mysql databases, the default display format of dates is YYYY-MM-DD, and if you want to display other date formats, you need to format the date using the Mysql date function DATE_FORMAT. For the formatting code of the Mysql date function DATE_FORMAT, see DATE_FORMAT on the Mysql official website.

Common Mysql date functions DATE_FORMAT in Mysql databases

1	SELECT DATE_FORMAT (date_col, "date format string") FROM tablename;

　　Mysql UNIX timestamp function UNIX_TIMESTAMP/FROM_UNIXTIME

Why do we need to introduce these two Mysql UNIX timestamp functions? The DATE_FORMAT function is only a Mysql date formatting function. when the format conversion is required between the PHP date function and the Mysql date function, there must be a bridge of communication, the UNIX timestamp format bridges the time between the two. the Mysql UNIX timestamp function UNIX_TIMESTAMP is mainly used to convert the current time or specified date and time value to the UNIX timestamp format, that is

1 2 3

SELECT UNIX_TIMESTAMP (); Or SELECT UNIX_TIMESTAMP ('2017-05-28 21:00:00 ');

The FROM_UNIXTIME function is mainly used to display the specified UNIX timestamp date format according to the specified date and time format string requirements, that is

1	FROM_UNIXTIME (unix_timestamp [, format])

The meaning of the format string is consistent with that of the Mysql date function DATE_FORMAT string.

　　Advantages of using UNIX timestamp date format in Mysql databases

Based on my personal experience, when you develop PHP, if you involve a large number of computing tasks on the front and back ends, we recommend that you design the Mysql database, the date and time format still uses the UNIX timestamp format. the advantage of the UNIX timestamp date format is that it is universal, so that the date functions in different formats can be converted to each other, and the date calculation efficiency is also higher. If the date in PHP development is more used for display, it is more intuitive to use the date display format YYYY-MM-DD inherent in Mysql. Of course, in the whole Mysql database design, the type of the date and time field still needs to be planned in an integrated manner. do not use different formats, which will cause great trouble for website development and post-maintenance.

So far, the introduction to the date and time format conversion function in Mysql date functions is complete. for PHP beginners, you only need to have a flexible understanding of the basic knowledge of Mysql date functions DATE_FORMAT, UNIX_TIMESTAMP, FROM_UNIXTIME, in PHP website development, there is basically no problem with date processing.