MySQL Data Type -- date and time, mysql DATA type --

MySQL Data Type -- date and time, mysql DATA type --

I. Blog Preface

I have been learning MySQL for some time. I still have some basic knowledge about MySQL. After learning this course, no matter whether you read books or materials on the Internet, the time and date types in the MySQL DATA type are always scanned, and you have never stopped to study it seriously. I recently borrowed a book about MysQL from the library, and I plan to study it comprehensively.

In the past, I was not familiar with the time and date data type, and I never used this type. In my project, I used the data stored in time. I used the int data type to store the time stamp. However, when studying MySQL optimization later, there is a principle that the data type with the minimum occupied space should be used for data storage. The int type is 4 bytes, And the TIMESTAMP is also 4 bytes. However, to use a date, the TIMESTAMP must be further converted, while the TIMESTAMP type data is not required.

Therefore, it is necessary to study every knowledge point carefully!

Ii. Overview of time and date data types

MySQL has multiple data types that indicate TIME and DATE, including YEAR, TIME, DATE, DATETIME, and TIMESTAMP. Each data type has a stored time and date format and a value range. Therefore, you must select the best data type when using the time and date data type.

Lists several data types:

Iii. Details

1. YEAR

Year is used to store the YEAR. Only one byte is required for storage. You can specify the year value in various formats when inserting data.

Common insert Format Parsing:

A and 4-digit string or number format, range: "1901 "~ "2155" indicates the value.

B. Two strings in the format of "00 "~ "99", "00 "~ "69" is converted to 20 ** (for example, "16" to "2016"), "70 "~ "99" is converted to 19 **

C. Double-digit format, range: 1 ~ 99,1 ~ 69 is converted to 20 ** (for example, 1 is converted to 2001), 70 ~ 99 is converted to 19 **

2. TIME

Time is used to store time information. It must be in three bytes. We can learn about the storage format and data range of time. HH hours are more than 24 hours a day, which can be more than eight hundred digits. This is because this type can be used not only to store a day, but also to record a period of time interval. So the hour is 24 hours. You can insert data in multiple formats.

Common insert Format Parsing:

A, "d hh: MM: SS" format string format. You can also use non-strict syntax inserts, such as "HH: MM: SS", "d hh: SS", and "MM: SS "," d hh ", and" SS "(D indicates the day, and the value range is 0 ~ 34 ). In the database, D will be converted to D * 24 + HH, and will be stored as an hour.

B. "HHMMSS": string format without intervals or number format without intervals in HHMMSS. Non-strict syntax insertion can be MMSS, SS, or MMSS.Note that:1,When the two insertion formats use non-strict syntax (that is, the time and second are incomplete, only the time, and so on), the rightmost data will be the default number of seconds, for example, when 2318 is inserted, the final database is 002318.2,Data in minutes and seconds must be meaningful. If MM is 65 or more than 59, an error will occur.

3. DATE

Date is used to store the date. If there is no time, three bytes are required for storage. You can insert data in multiple formats.

Common insert Format Parsing:

A, "YYYY-MM-DD", "YYYYMMDD ".

B, "YY-MM-DD", "YYMMDD", to insert a two-digit year, the main difference here "00 ~ 69 and 70 ~ 99 ", the former indicates 20 **, and the latter indicates 19 **.

C. digit formats: YY-MM-dd and yymmdd. two-digit year inserted, the same as above.

D. Use Function insert, CURRENT_DATE (), NOW ()

4. DATETIME

Datetime is used to store date and time data, which requires eight bytes. You can insert data in multiple formats.

Common insert Format Parsing:

A, "YYYY-MM-DD HH: MM: SS", "YYYYMMDDHHMMSS", the final inserted database is 23:55:59 format.

B, "YY-MM-DD HH: MM: SS", "YYMMDDHHMMSS", two-digit insert year, mainly distinguish "00 ~ 69 and 70 ~ 99 ", the same as above.

C. Insert numbers in the format of YYYYMMDDHHMMSS and YYMMDDHHMMSS. The two-digit year insertion problem is the same as the above.

5. TIMESTAMP

Timestamp is used to store Date and Time data. The format is the same as that of datetime. The main difference between timestamp and datetime is the value range. The timestamp storage requires four bytes. The value range is "00:00:01" UTC ~ "2038-01-19 03:14:07" UTC, and datetime value range: "1000-01-01 00:00:00 "~ "9999-12-31 23:59:59" (UTC: Coordinated Universal Time World Time Standard ). There are also multiple insert formats.

Common insert Format Parsing:

Consistent with DATETIME, refer to the above.

Iv. Summary

Understanding the date and time data types of MySQL is necessary to select a suitable storage type. If you can select YEAR for only the storage YEAR, TIME for only the storage TIME, or the full date and TIME for storage, you can select the DATATIME or TIMESTAMP data type as needed.

 

(The above are some of your own opinions. If you have any shortcomings or errors, please point them out)

Author: Yi ye Fengfeng

Disclaimer: This blog post is original and only represents the point of view or conclusion I have summarized at a certain time in my work and study. When reprinting, please provide the original article link clearly on the article page.