MySQL data type--date time

First, the blog preface

Since the contact learning MySQL for some time, the basic knowledge of MySQL still have a certain understanding. In this way to learn, every time whether reading books or on-line data, for the MySQL data type of time and date type is always swept away, did not stop to identify serious research study. Recently I borrowed a book about MySQL from the library, and I intend to study it thoroughly.

Before, I was not very cold about the time-date data type, nor did I ever use this type. I use the data of the storage time in my project, I use int integer data type to store, that is to store timestamp. However, when learning MySQL optimization later, there is a principle that the data should be stored in the smallest space-consuming data type. The int type is 4 bytes and timestamp is 4 bytes, but the timestamp needs to be further converted when the date is needed, and the timestamp type data is not required.

So it is necessary to learn to understand every point of knowledge seriously!

Ii. general overview of time and date data types

There are several types of data that represent time and date in MySQL, including year, hour, date, DATETIME, timestamp, and so on. Each data type has a stored time-date format, and a range of values, so you need to pick the best data type when using the time-date data type.

Several data types are listed:

Third, the fine talk

1. Year

Year is used for storage years, and only one byte is required for storage, and you can use various formats to specify the yearly value when inserting data.

Common Insert Format parsing:

A, four-bit string, or number format, the range is "1901" ~ "2155", how much is the number.

B, two-bit string format, the range is "00" ~ "99", "00" ~ "69" is converted to 20** (for example: "16" into "2016"), "70" ~ "99" is converted to 19**

C, two-bit number format, the range of 1~99,1~69 is converted to 20** (for example: 1 to 2001), 70~99 is converted to 19**

2. Time

Time is used to store temporal information, which requires three bytes to be stored. You can learn about the storage format of time, as well as the range of data. Where HH hours are greater than 24 hours a day and can be more than 800 digits. This is because this type can be used not only to store a certain time of the day but also to record intervals over time. So the hour is 24 hours. There can be multiple formats for inserting data.

Common Insert Format parsing:

A, "D HH:MM:SS" format string format. You can also use non-strict syntax insertions, such as "HH:MM:SS", "D hh:ss", "Mm:ss", "D HH", "SS" (d for days, and a value range of 0~34). When the database is deposited, D will be converted to D*24+HH and will eventually be stored as an hour.

B, "Hhmmss" no interval of the string format or HHMMSS no interval number format, non-strict syntax insertion can be, MMSS, SS, "MMSS" and so on. It is important to note that: 1, the two insert formats with non-strict syntax (that is, the time is incomplete, only cent and so on), the rightmost data will default to the number of seconds, such as inserting 2318, the final storage is 002318. 2, minutes and seconds of data to have a practical significance, if · If the MM is 65 over 59, an error will occur.

3. DATE

Date is used to store dates, there is no time section, and three bytes are required for storage. There can be multiple formats for inserting data.

Common Insert Format parsing:

A, "Yyyy-mm-dd", "YYYYMMDD".

B, "Yy-mm-dd", "YYMMDD", with two digits to insert the year, here the main difference between "00~69" and "70~99", the former represents 20**, the latter represents 19**.

C, number format YY-MM-DD, YYMMDD, two digits inserted year, different from the same.

D, using function insertion, current_date (), now ()

4. DATETIME

DateTime is used to store datetime data, which requires eight bytes to be stored. There can be multiple formats for inserting data.

Common Insert Format parsing:

A, "Yyyy-mm-dd HH:MM:SS", "Yyyymmddhhmmss", the final insert database is 2017-01-17 23:55:59 format.

B, "Yy-mm-dd HH:MM:SS", "YYMMDDHHMMSS", two digits into the year, the main distinction between "00~69" and "70~99", the same as the above.

C, number format insertion, YYYYMMDDHHMMSS, YYMMDDHHMMSS, two-digit insertion year the problem is consistent with the above.

5, TIMESTAMP

Timestamp is used to store datetime data, as is the case with datetime stored data formats, where the main difference is in the range of values. Timestamp storage requires four bytes, and its value range is "1970-01-01 00:00:01" UTC ~ "2038-01-19 03:14:07" UTC, and the datetime value range is "1000-01-01 00:00:00" ~ " 9999-12-31 23:59:59 "(Utc:coordinated Universal time World standard). There are also several insert formats.

Common Insert Format parsing:

Consistent with DateTime, you can refer to the above.

Iv. Summary

Understanding the date-time data type of MySQL is necessary to choose a suitable storage type. If you can select year only for the storage years, you can choose time only, or you need to store the full datetime, you can choose the datatime or timestamp data type depending on the situation.

(These are some of their own ideas, if there is insufficient or wrong place please point out)

One leaf with the wind

Statement: This blog article for the original, only represents my work in the study in a certain time to summarize the views or conclusions. When reprinted, please give the original link in the obvious position of the article page.

MySQL data type--date time