Detailed explanation of MySQL time and date functions

MySQL is a popular relational database management system. It provides a rich library of time and date functions that can easily calculate, format and convert time and date. In this article, we will introduce in detail the usage and precautions of commonly used time and date functions in MySQL.

1. NOW() function

The NOW() function returns the current system date and time. It has no parameters and can be called directly. For example:

SELECT NOW();

The result returned is similar to: 2022-01-01 23:59:59

2. DATE() function

DATE() function extracts the date part from the timestamp. Its parameters can be dates, timestamps, or strings. For example:

SELECT DATE('2022-01-01 23:59:59');

The returned result is: 2022-01-01

3. TIME() function

TIME() function extracts the time part from the timestamp. Its parameters can be dates, timestamps, or strings. For example:

SELECT TIME('2022-01-01 23:59:59');

The returned result is: 23:59:59

4. YEAR() function

The YEAR() function extracts the year part from a date or timestamp. Its parameters can be dates, timestamps, or strings. For example:

SELECT YEAR('2022-01-01 23:59:59');

The result returned is: 2022

5. MONTH() function

MONTH() function extracts the month part from a date or timestamp. Its parameters can be dates, timestamps, or strings. For example:

SELECT MONTH('2022-01-01 23:59:59');

The result returned is: 1

6. DAY() function

The DAY() function extracts the day part from a date or timestamp. Its parameters can be dates, timestamps, or strings. For example:

SELECT DAY('2022-01-01 23:59:59');

The result returned is: 1

7. HOUR() function

The HOUR() function extracts the hour part from the timestamp. Its parameters can be dates, timestamps, or strings. For example:

SELECT HOUR('2022-01-01 23:59:59');

The result returned is: 23

8. MINUTE() function

MINUTE() function extracts the minutes part from the timestamp. Its parameters can be dates, timestamps, or strings. For example:

SELECT MINUTE('2022-01-01 23:59:59');

The result returned is: 59

9. SECOND() function

The SECOND() function extracts the seconds part from the timestamp. Its parameters can be dates, timestamps, or strings. For example:

SELECT SECOND('2022-01-01 23:59:59');

The result returned is: 59

10. DATE_FORMAT() function

The DATE_FORMAT() function can format a date or timestamp into a specified format. Its first parameter is a date or timestamp, and its second parameter is a format string. For example:

SELECT DATE_FORMAT('2022-01-01 23:59:59','%Y/%m/%d %H:%i:%s');

The returned result is: 2022/01/01 23:59:59

In the format string, %Y represents the year, %m represents the month, %d represents the number of days, %H represents the hour, and %i represents Minutes, %s represents seconds, other symbols and text will be output as is.

Note that the date format in MySQL is YYYY-MM-DD and the time format is HH:MM:SS. If you want to format it in other formats, you need to use the DATE_FORMAT() function.

Summary

The above is the introduction and use of commonly used time and date functions in MySQL. When using these functions, you need to pay attention to the format of the time and date to avoid errors. Proper use of these functions will greatly improve development efficiency and make data processing more convenient and faster.

The above is the detailed content of Detailed explanation of MySQL time and date functions. For more information, please follow other related articles on the PHP Chinese website!