Shulou(Shulou.com)06/01 Report--

There are several data types in MySQl that represent dates and times. Where YEAR represents the year, DATE represents the date, TIME represents the time, and DATETIME and TIMESTAMP represent the date and practice. Their comparisons are as follows

YEAR, the number of bytes is 1, and the value range is "1901 Murray 2155".

DATE, the number of bytes is 4, and the value range is "1000-01-01 murmur9999-12-31".

TIME, the number of bytes is 3, and the range of values is "- 838 glue59Rose 59MUR 59MULI 838MULTHING 59RAPHY".

DATETIME, the number of bytes is 8. The range of values is "1000-01-01 00 DATETIME. 0000 horse 0000 color 9999-12-31 23:59:59".

TIMESTAMP, the number of bytes is 4, and the value range is "19700101080001murmur20380119111407".

When the insertion value is outside the range of valid values, the system reports an error and inserts the zero value into the database.

YEAR Typ

There are three ways to copy a YEAR type.

The first is to insert a 4-digit string or 4-digit number directly.

The second is to insert a 2-bit string. In this case, if you insert'00', it is equivalent to inserting 2000 '2069; if you insert' 70', it is equivalent to inserting 1970'99. In the second case, if the insert is'0', the effect is the same as that of '00', which represents the year 2000.

The third is to insert a 2-digit number, which is different from the second (inserting a two-digit string) except that if you insert a digit 0, it represents 0000, not 2000. So when assigning a value to the YEAR type, be sure to distinguish between 0 and'0. although there is a difference in quotation marks, the actual effect is indeed 2000 years different.

TIME Typ

The TIME type is expressed as "hours: minutes: seconds", and although the hour range is generally 0: 23, MySQL expands the hour range of TIME and supports negative values to indicate some special time intervals.

For TIME type assignments, the standard format is' HH:MM:SS', but not necessarily this format.

If you insert the'D HH:MM:SS' format, it's like inserting'(D*24+HH): MM:SS'. For example, the insertion of '223 / 50 / 50 / 50 is equivalent to the insertion of / / 71 / 50 / 50 / 50 / 7.

If you insert the 'HH:MM' or' SS' format, the effect is that the values of other unrepresented bits are assigned zero values. For example, the insertion of'30'is equivalent to the insertion of'00-0-0-0-1-0-1-0-1-0-1-3-1-3-3, while the insertion of'11-1-1-5-5-2-4 is equivalent to the insertion of'11-1, 5-5, 5, 4, 4, 5, 4, 4, 4, 4, 4, 3, 4, 4, 4, 3, 4, 3, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 3, 4, 5, 4, 5, 4, 5, 4, 5, 4, 3, 4, 3, 4, 4, 4, 3, 4, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4

You can also insert'D HH' 'and' D HH:MM', effects. You can deduce from the above example.

In MySQl, for the 'HHMMSS' format, the system can automatically convert to a standard format.

If we want to insert the time of the current system, we can insert CURRENT_TIME or NOW (). The TIME type is only 3 bytes, and it works best if you just store time data.

DATE Typ

MySQL displays values of type DATE in YYYY-MM-DD format, which can be maintained when the data is inserted. In addition, MySQL also supports some loose syntax formats, the delimiter "-" can use "@", "." Waiting for many rich people to replace it.

You can also use the "YY-MM-DD" format when inserting data, and the rules for converting YY to the corresponding year are similar to the YEAR type.

If we want to insert the time of the current system, we can insert CURRENT_DATE or NOW ().

DATETIME Typ

The standard format is "YYYY-MM-DD HH:MM:SS", and the specific assignment method is similar to the various types of methods above.

TIMESTAMP Typ

The value range of TIMESTAMP is relatively small, but not as large as that of DATETIME, so the input value must be within the range of TIMESTAMP. Its insertion is similar to inserting other date and time data types.

So how does the TIMESTAMP type insert the current time? First, you can use CURRENT_TIMESTAMP; second, enter NULL, the system automatically enters the current TIMESTAMP; third, without any input, the system automatically enters the current TIMESTAMP.

There is also a very special point: the value of TIMESTAMP is related to the time zone.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.