What are the date types in mysql?

Mysql date types include DATE, TIME, DATETIME, TIMESTAMP, and YEAR. Detailed introduction: 1. DATE, used to store dates, can only store dates, not the time part; 2. TIME, used to store time, the format is only able to store time, not the date part; 3. DATETIME, used to store Date and time, which can store date and time parts, accurate to seconds; 4. TIMESTAMP, used to store date and time; 5. YEAR, used to store years, etc.

The operating environment of this article: Windows 10 system, MySQL version 8.0.32, Dell g3 computer.

MySQL provides a variety of date and time types, and you can choose the appropriate type according to different needs. The following are the commonly used date and time types in MySQL:

1. DATE: is used to store dates in the format YYYY-MM-DD. Only the date can be stored, not the time part.

2. TIME: is used to store time in the format of HH:MM:SS. Only the time can be stored, not the date part.

3. DATETIME: Used to store date and time in the format YYYY-MM-DD HH:MM:SS. Ability to store date and time parts down to the second.

4. TIMESTAMP: Used to store date and time in the format YYYY-MM-DD HH:MM:SS. Similar to the DATETIME type, but has a wider range, from January 1, 1970 to December 31, 2038.

5. YEAR: is used to store the year, the format is YYYY. Only the year can be stored, not the month, date and time.

In addition to the above commonly used date and time types, MySQL also provides some other date and time types, such as:

1. YEAR(4): used to store four-digit years, format for YYYY. The storable range is from 1901 to 2155.

2. YEAR(2): Used to store two-digit years in the format of YY. Storage range is from 70 to 69 years.

3. TIMESTAMP(N): used to store date and time in the format YYYY-MM-DD HH:MM:SS.NS. N represents the number of digits in the decimal part, which can be specified from 0 to 6 digits.

4. TIME(N): used to store time in the format of HH:MM:SS.NS. N represents the number of digits in the decimal part, which can be specified from 0 to 6 digits.

In addition to these standard date and time types, MySQL also provides some non-standard date and time types, such as INTERVAL, YEAR_MONTH, and DAY_HOUR, for specific time calculation and storage requirements.

Using appropriate date and time types can improve storage efficiency and query performance, while also ensuring data accuracy. When designing a database, you need to consider which date and time type to use based on actual needs.

The above is the detailed content of What are the date types in mysql?. For more information, please follow other related articles on the PHP Chinese website!